Bug 4016 - merge table only optimises for point query
Summary: merge table only optimises for point query
Status: RESOLVED FIXED
Alias: None
Product: SQL
Classification: Unclassified
Component: all (show other bugs)
Version: 11.21.19 (Jul2015-SP4)
Hardware: Other Linux
: Normal normal
Assignee: SQL devs
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2016-06-06 02:20 CEST by Stefan de Konink
Modified: 2016-12-21 13:07 CET (History)
1 user (show)



Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Stefan de Konink 2016-06-06 02:20:45 CEST
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 1 Niels Nes cwiconfidential 2016-08-19 16:21:39 CEST
in lists and ranges are handled by the current partition elimination in the default branch.