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

merge table only optimises for point query #4016

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

merge table only optimises for point query #4016

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

Comments

@monetdb-team
Copy link

Date: 2016-06-06 02:20:45 +0200
From: @skinkie
To: SQL devs <>
Version: 11.21.19 (Jul2015-SP4)
CC: @njnes

Last updated: 2016-12-21 13:07:34 +0100

Comment 22195

Date: 2016-06-06 02:20:45 +0200
From: @skinkie

User-Agent: Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/52.0.2739.0 Safari/537.36
Build Identifier:

When using a merge table with analyze and read only awesome performance can be achieved using a point query;

explain select count(*) from punctdep where operatingday = '2015-06-02';

function user.main():void;
X_2 := sql.mvc();
X_6:bat[:date] := sql.bind(X_2,"sys","punctdep_20150602","operatingday",0);
C_3:bat[:oid] := sql.tid(X_2,"sys","punctdep_20150602");
C_9 := algebra.subselect(X_6,C_3,"2015-06-02","2015-06-02",true,false,false);
X_12 := algebra.projection(C_9,X_6);
X_13 := aggr.count(X_12);
sql.resultSet("sys.L1","L1","bigint",64,0,7,X_13);
end user.main;

Now this trivial examples still explodes in the query plan:

explain select count() from punctdep where operatingday = '2015-06-02' or operatingday = '2015-06-03';
explain select count(
) from punctdep where operatingday IN ('2015-06-02', '2015-06-03');

Reproducible: Always

MonetDB 5 server v11.24.0 (64-bit, 64-bit oids, 128-bit integers)
This is an unreleased version
Copyright (c) 1993-July 2008 CWI
Copyright (c) August 2008-2016 MonetDB B.V., all rights reserved
Visit http://www.monetdb.org/ for further information
Found 62.8GiB available memory, 8 available cpu cores
Libraries:
libpcre: 8.38 2015-11-23 (compiled with 8.38)
openssl: OpenSSL 1.0.2h 3 May 2016 (compiled with OpenSSL 1.0.2h 3 May 2016)
libxml2: 2.9.4 (compiled with 2.9.4)
Compiled by: skinkie@chamechaude (x86_64-pc-linux-gnu)
Compilation: gcc -O3 -pipe -Werror -Wall -Wextra -W -Werror-implicit-function-declaration -Wpointer-arith -Wdeclaration-after-statement -Wundef -Wformat=2 -Wno-format-nonliteral -Winit-self -Winvalid-pch -Wmissing-declarations -Wmissing-format-attribute -Wmissing-prototypes -Wold-style-definition -Wpacked -Wunknown-pragmas -Wvariadic-macros -fstack-protector-all -Wstack-protector -Wpacked-bitfield-compat -Wsync-nand -Wjump-misses-init -Wmissing-include-dirs -Wlogical-op -Wunreachable-code
Linking : /usr/x86_64-pc-linux-gnu/bin/ld -m elf_x86_64

Comment 22308

Date: 2016-08-19 16:21:39 +0200
From: @njnes

in lists and ranges are handled by the current partition elimination in the default branch.

@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