Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

creating temp table kills performance of the original query #6577

Closed
monetdb-team opened this issue Nov 30, 2020 · 0 comments
Closed

creating temp table kills performance of the original query #6577

monetdb-team opened this issue Nov 30, 2020 · 0 comments
Labels
bug Something isn't working normal SQL

Comments

@monetdb-team
Copy link

Date: 2018-04-13 00:58:37 +0200
From: Anton Kravchenko <<kravchenko.anton86>>
To: SQL devs <>
Version: 11.29.3 (Mar2018)
CC: @njnes

Last updated: 2018-06-20 11:02:14 +0200

Comment 26364

Date: 2018-04-13 00:58:37 +0200
From: Anton Kravchenko <<kravchenko.anton86>>

User-Agent: Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/65.0.3325.181 Safari/537.36
Build Identifier:

creating temp table constrains the original query to use a single CPU

Reproducible: Always

Steps to Reproduce:

  1. run time=4.6s, 4 CPUs are used
    select count(t1.v1) from t1, t2 where t1.v1=t2.v1;

  2. run time=15s, 1 CPU is used
    create temp table t3 as
    (select count(t1.v1) from t1, t2 where t1.v1=t2.v1)
    on commit preserve rows;

  3. run time=15s, 1 CPU is used
    create temp table t3 as
    (select t1.v1 from t1, t2 where t1.v1=t2.v1)
    on commit preserve rows;

Tables t1, t2 were created from:
create or replace function test1() RETURNS TABLE (v0 INT, v1 INT)
LANGUAGE PYTHON {
import numpy as np
nrows=100000000
i_var=np.random.randint(1,10000000, (100000000,))
return [[i for i in range(nrows)],i_var]
};
create table t1 as (select * from test1()) with data;
create table t2 as (select distinct v1 from t1) with data;

Actual Results:

run time=15s, 1 CPU is used
create temp table t3 as
(select t1.v1 from t1, t2 where t1.v1=t2.v1)
on commit preserve rows;

Expected Results:

run time=4.6s, 4 CPUs are used
create temp table t3 as
(select t1.v1 from t1, t2 where t1.v1=t2.v1)
on commit preserve rows;

MonetDB 5 server v11.29.3 "Mar2018" (64-bit, 128-bit integers)
Copyright (c) 1993 - July 2008 CWI
Copyright (c) August 2008 - 2018 MonetDB B.V., all rights reserved
Visit https://www.monetdb.org/ for further information
Found 31.3GiB available memory, 4 available cpu cores
Libraries:
libpcre: 8.32 2012-11-30 (compiled with 8.32)
openssl: OpenSSL 1.0.2k 26 Jan 2017 (compiled with OpenSSL 1.0.2l 25 May 2017)
libxml2: 2.9.1 (compiled with 2.9.1)
Compiled by: akravchenko@cent7-1 (x86_64-pc-linux-gnu)
Compilation: gcc -std=gnu99 -O3 -fomit-frame-pointer -pipe -D_FORTIFY_SOURCE=2
Linking : /usr/bin/ld -m elf_x86_64 -Wl,-Bsymbolic-functions

Comment 26414

Date: 2018-04-29 11:39:19 +0200
From: MonetDB Mercurial Repository <>

Changeset dede692aec19 made by Pedro Ferreira pedro.ferreira@monetdbsolutions.com in the MonetDB repo, refers to this bug.

For complete details, see https//devmonetdborg/hg/MonetDB?cmd=changeset;node=dede692aec19

Changeset description:

Added tests for bugs 6577 and 6594

Comment 26436

Date: 2018-05-11 10:18:53 +0200
From: MonetDB Mercurial Repository <>

Changeset f3fcfcad3fbc made by Niels Nes niels@cwi.nl in the MonetDB repo, refers to this bug.

For complete details, see https//devmonetdborg/hg/MonetDB?cmd=changeset;node=f3fcfcad3fbc

Changeset description:

fixes for bug #6577 and 6600

Comment 26440

Date: 2018-05-11 13:15:26 +0200
From: @njnes

fixed, ie also update queries are now using mitosis

@monetdb-team monetdb-team added bug Something isn't working normal SQL labels Nov 30, 2020
@sjoerdmullender sjoerdmullender added this to the Ancient Release milestone Feb 7, 2022
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
bug Something isn't working normal SQL
Projects
None yet
Development

No branches or pull requests

2 participants