- developers-list - monetdb.org

Re: MonetDB: default - Progress bar: Ignore comments in trace output
by Ying Zhang 24 Feb '15

24 Feb '15

Hai Hannes, Compiling default branch complains about "‘profiler_querystart’ may be used uninitialized": libtool: compile: gcc -DHAVE_CONFIG_H -I. -I/ufs/zhang/monet/default/clients/mapiclient -I../.. -I/ufs/zhang/monet/default/clients/mapiclient -I../mapilib -I/ufs/zhang/monet/default/clients/mapiclient/../mapilib -I../../common/options -I/ufs/zhang/monet/default/clients/mapiclient/../../common/options -I../../common/stream -I/ufs/zhang/monet/default/clients/mapiclient/../../common/stream -I../R/MonetDB.R/src -I/ufs/zhang/monet/default/clients/mapiclient/../R/MonetDB.R/src -DLIBMCUTIL -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 -D_FORTIFY_SOURCE=2 -D_REENTRANT -c /ufs/zhang/monet/default/clients/mapiclient/prompt.c -fPIC -DPIC -o .libs/libmcutil_la-prompt.o In file included from /ufs/zhang/monet/default/clients/mapiclient/profiler-dummy.c:5:0: /ufs/zhang/monet/default/clients/mapiclient/../R/MonetDB.R/src/profiler.c: In function ‘profiler_thread’: /ufs/zhang/monet/default/clients/mapiclient/../R/MonetDB.R/src/profiler.c:219:59 error: ‘profiler_querystart’ may be used uninitialized in this function [-Werror=maybe-uninitialized] if (profiler_msgs_expect > 0 && (profiler_tsms() - profiler_querystart) > 500) { ^ This seems to only happen if I use the --enable-optimize --disable-debug configure options. Can I just initialise profiler_querystart with 0? Thanks! Jennie > On Feb 20, 2015, at 18:39, Hannes Muehleisen <commits(a)monetdb.org> wrote: > > Changeset: cde6ad120a72 for MonetDB > URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=cde6ad120a72 > Modified Files: > clients/R/MonetDB.R/src/profiler.c > Branch: default > Log Message: > > Progress bar: Ignore comments in trace output > > > diffs (13 lines): > > diff --git a/clients/R/MonetDB.R/src/profiler.c b/clients/R/MonetDB.R/src/profiler.c > --- a/clients/R/MonetDB.R/src/profiler.c > +++ b/clients/R/MonetDB.R/src/profiler.c > @@ -177,6 +177,9 @@ static void* profiler_thread(void* param > recvd = read(profiler_socket, buf, sizeof(buf)); > if (recvd > 0) { > buf[recvd] = 0; > + if (buf[0]== '#') { > + continue; > + } > mapi_line_split(buf, elems, TRACE_NCOLS); > if (strncmp(elems[TRACE_COL_STATEFL], "done", 4) != 0) { > continue; > _______________________________________________ > checkin-list mailing list > checkin-list(a)monetdb.org > https://www.monetdb.org/mailman/listinfo/checkin-list

2 2

Re: MonetDB: default - mclient progress meter part one, mal statemen...
by Stefan Manegold 24 Feb '15

24 Feb '15

This fails to compile, at least when BUILDDIR != SOURCEDIR: ======== make[5]: Entering directory `/net/rome.ins.cwi.nl/export/scratch1/manegold/Monet/HG/default/builds/_/clients/mapiclient' /bin/sh ../../libtool --tag=CC --mode=compile gcc -DHAVE_CONFIG_H -I. -I/ufs/manegold/_/Monet/HG/default/source/MonetDB/clients/mapiclient -I../.. -I/ufs/manegold/_/Monet/HG/default/source/MonetDB/clients/mapiclient -I../mapilib -I/ufs/manegold/_/Monet/HG/default/source/MonetDB/clients/mapiclient/../mapilib -I../../common/options -I/ufs/manegold/_/Monet/HG/default/source/MonetDB/clients/mapiclient/../../common/options -I../../common/stream -I/ufs/manegold/_/Monet/HG/default/source/MonetDB/clients/mapiclient/../../common/stream -I../R/MonetDB.R/src/ -I/ufs/manegold/_/Monet/HG/default/source/MonetDB/clients/mapiclient/../R/MonetDB.R/src/ -DLIBMCUTIL -g -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 -D_REENTRANT -c -o libmcutil_la-dump.lo `test -f 'dump.c' || echo '/ufs/manegold/_/Monet/HG/default/source/MonetDB/clients/mapiclient/'`dump.c libtool: compile: gcc -DHAVE_CONFIG_H -I. -I/ufs/manegold/_/Monet/HG/default/source/MonetDB/clients/mapiclient -I../.. -I/ufs/manegold/_/Monet/HG/default/source/MonetDB/clients/mapiclient -I../mapilib -I/ufs/manegold/_/Monet/HG/default/source/MonetDB/clients/mapiclient/../mapilib -I../../common/options -I/ufs/manegold/_/Monet/HG/default/source/MonetDB/clients/mapiclient/../../common/options -I../../common/stream -I/ufs/manegold/_/Monet/HG/default/source/MonetDB/clients/mapiclient/../../common/stream -I../R/MonetDB.R/src/ -I/ufs/manegold/_/Monet/HG/default/source/MonetDB/clients/mapiclient/../R/MonetDB.R/src/ -DLIBMCUTIL -g -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 -D_REENTRANT -c /ufs/manegold/_/Monet/HG/default/source/MonetDB/clients/mapiclient/dump.c -fPIC -DPIC -o .libs/libmcutil_la-dump.o cc1: error: ../R/MonetDB.R/src/: No such file or directory [-Werror] cc1: all warnings being treated as errors make[5]: *** [libmcutil_la-dump.lo] Error 1 make[5]: Leaving directory `/net/rome.ins.cwi.nl/export/scratch1/manegold/Monet/HG/default/builds/_/clients/mapiclient' ======== Stefan ----- Original Message ----- > Changeset: 18471e6cd453 for MonetDB > URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=18471e6cd453 > Added Files: > clients/mapiclient/mapisplit-dummy.c > clients/mapiclient/profiler-dummy.c > Modified Files: > clients/mapiclient/Makefile.ag > clients/mapiclient/mclient.c > Branch: default > Log Message: > > mclient progress meter part one, mal statement progress > > > diffs (148 lines): > > diff --git a/clients/mapiclient/Makefile.ag b/clients/mapiclient/Makefile.ag > --- a/clients/mapiclient/Makefile.ag > +++ b/clients/mapiclient/Makefile.ag > @@ -16,7 +16,7 @@ > # All Rights Reserved. > > MTSAFE > -INCLUDES = ../mapilib ../../common/options ../../common/stream > $(READLINE_INCS) > +INCLUDES = ../mapilib ../../common/options ../../common/stream > ../R/MonetDB.R/src/ $(READLINE_INCS) > > lib_mcutil = { > NOINST > @@ -24,7 +24,7 @@ lib_mcutil = { > } > > bin_mclient = { > - SOURCES = mclient.c ReadlineTools.c ReadlineTools.h > + SOURCES = mclient.c ReadlineTools.c ReadlineTools.h mapisplit-dummy.c > profiler-dummy.c > LIBS = libmcutil ../mapilib/libmapi \ > ../../common/stream/libstream \ > $(READLINE_LIBS) \ > diff --git a/clients/mapiclient/mapisplit-dummy.c > b/clients/mapiclient/mapisplit-dummy.c > new file mode 100644 > --- /dev/null > +++ b/clients/mapiclient/mapisplit-dummy.c > @@ -0,0 +1,5 @@ > +/* > +This refers to ../R/MonetDB.R/src/mapisplit.c . We cannot have this file > here, > +as the R connector needs to be able to compile stand-alone. > +*/ > +#include "mapisplit.c" > \ No newline at end of file > diff --git a/clients/mapiclient/mclient.c b/clients/mapiclient/mclient.c > --- a/clients/mapiclient/mclient.c > +++ b/clients/mapiclient/mclient.c > @@ -78,6 +78,8 @@ > #define S_ISREG(m) (((m) & S_IFMT) == S_IFREG) > #endif > > +#include "profiler.h" > + > enum modes { > MAL, > SQL, > @@ -1934,14 +1936,13 @@ doFileBulk(Mapi mid, FILE *fp) > } > } > timerResume(); > - > if (hdl == NULL) { > hdl = mapi_query_prep(mid); > CHECK_RESULT(mid, hdl, buf, continue, buf); > } > > assert(hdl != NULL); > - > + profiler_arm(); > mapi_query_part(hdl, buf, length); > CHECK_RESULT(mid, hdl, buf, continue, buf); > > @@ -2722,6 +2723,8 @@ doFile(Mapi mid, const char *file, int u > > if (hdl == NULL) { > timerStart(); > + profiler_arm(); > + > hdl = mapi_query_prep(mid); > CHECK_RESULT(mid, hdl, buf, continue, buf); > } else > @@ -2861,6 +2864,7 @@ usage(const char *prog, int xit) > fprintf(stderr, " -w nr | --width=nr for pagination\n"); > fprintf(stderr, " -D | --dump create an SQL dump\n"); > fprintf(stderr, " -N | --inserts use INSERT INTO > statements when dumping\n"); > + fprintf(stderr, " -P | --progress show progress bar\n"); > fprintf(stderr, "The file argument can be - for stdin\n"); > exit(xit); > } > @@ -2887,6 +2891,7 @@ main(int argc, char **argv) > int interactive = 0; > int has_fileargs = 0; > int option_index = 0; > + int progress = 0; > int settz = 1; > int autocommit = 1; /* autocommit mode default on */ > struct stat statb; > @@ -2912,6 +2917,7 @@ main(int argc, char **argv) > {"pager", 1, 0, '|'}, > #endif > {"port", 1, 0, 'p'}, > + {"progress", 0, 0, 'P'}, > {"rows", 1, 0, 'r'}, > {"statement", 1, 0, 's'}, > #if 0 > @@ -2970,7 +2976,7 @@ main(int argc, char **argv) > #if 0 > "t" > #endif > - "w:r:p:s:Xu:vzH?", > + "w:r:p:s:Xu:vzHP?", > long_options, &option_index)) != -1) { > switch (c) { > case 0: > @@ -3114,6 +3120,9 @@ main(int argc, char **argv) > case 'z': > settz = 0; > break; > + case 'P': > + progress = 1; > + break; > case '?': > /* a bit of a hack: look at the option that the > * current `c' is based on and see if we recognize > @@ -3226,6 +3235,16 @@ main(int argc, char **argv) > } > } > > + // switch on progress bars > + if (mode == SQL && progress) { > + char* buf = malloc(100); > + int port = profiler_start(); > + sprintf(buf, "CALL profiler_openstream('127.0.0.1', %d)", port); > + mapi_query(mid, buf); > + sprintf(buf, "CALL profiler_stethoscope(0)"); > + mapi_query(mid, buf); > + } > + > /* give the user a welcome message with some general info */ > if (!has_fileargs && command == NULL && isatty(fileno(stdin))) { > char *lang; > @@ -3296,6 +3315,7 @@ main(int argc, char **argv) > /* execute from command-line, need interactive to know whether > * to keep the mapi handle open */ > timerStart(); > + profiler_arm(); > c = doRequest(mid, command); > timerEnd(); > } > diff --git a/clients/mapiclient/profiler-dummy.c > b/clients/mapiclient/profiler-dummy.c > new file mode 100644 > --- /dev/null > +++ b/clients/mapiclient/profiler-dummy.c > @@ -0,0 +1,5 @@ > +/* > +This refers to ../R/MonetDB.R/src/profiler.c . We cannot have this file > here, > +as the R connector needs to be able to compile stand-alone. > +*/ > +#include "profiler.c" > \ No newline at end of file > _______________________________________________ > checkin-list mailing list > checkin-list(a)monetdb.org > https://www.monetdb.org/mailman/listinfo/checkin-list > -- | Stefan.Manegold(a)CWI.nl | DB Architectures (DA) | | www.CWI.nl/~manegold/ | Science Park 123 (L321) | | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) |

1 2

About merovingian and the MySQL protocol
by Fabian Groffen 23 Feb '15

23 Feb '15

Hiya all, A long time ago, I examined the MySQL client-server protocol, to understand why it is performing so well. This resulted in the idea (back then) to create a MySQL server implementation in merovingian, that proxied commands to mserver/mapi calls. This to possibly gain from performance benefits, but obviously more to open up the world of MySQL clients that exist to MonetDB. A minimalistic start was added to the code [1], but nothing more than that ever emerged. Years later, I ended up at a similar quest, one where I had to implement the MySQL server protocol and perform the rituals to make a MySQL client believe it is dealing with a proper server [2]. Objective is different here (some will recognise merovingian's multiplex-funnel feature), but the codepath is largely re-usable for the purpose of the above. Being rusty with the MonetDB code-base, not having followed any of its developments lately, whilst on this island in the sun, I'm wondering if it makes sense to implement the first at all, or whether it is better to simply remove the if-deffed code, for it is incomplete. Opinions? Best, Fabian [1] http://dev.monetdb.org/hg/MonetDB/file/fb8e50da2917/tools/merovingian/daemo… [2] https://github.com/grobian/mysqlpq

1 0

MonetDB in Fedora 21 and 22, and EPEL 7
by Jan Holčapek 10 Feb '15

10 Feb '15

Hello, I am aware that MonetDB is available as native Fedora 18-20 [1] and EPEL 6 [2] packages. However, I am missing native Fedora 21-22, as well as EPEL 7 packages. Would you find it beneficial if I try to push MonetDB directly to Fedora/EPEL packages, ie. it would have a record in Fedora Package Database [3] and be built on Fedora infrastructure? That said, I would become a mere package maintainer for the two distributions. Cheers Jan [1] https://www.monetdb.org/Documentation/UserGuide/Downloads/Fedora [2] https://www.monetdb.org/downloads/epel/ [3] https://admin.fedoraproject.org/pkgdb/

2 1

Adapting code Feb2013 -> Oct2014
by Roberto Cornacchia 09 Feb '15

09 Feb '15

Hi there, I have some custom code running on Feb2013 release. As I'm now trying to move to Oct2014, I need to update this custom code. MonetDB API has changed a bit in the meantime, for example if I just try to compile my code within Oct2014, I get error: implicit declaration of function 'ALGfetch' Indeed, looking at the previous commits, ALGfetch is one of the interfaces that have been removed. Question: Is there any documentation that can help me see more clearly what changed and how to adapt the code? Or is browsing the commit history my only resource? Thanks, Roberto

2 3

Announcement: New Oct2014-SP2 Bugfix release of MonetDB suite
by Sjoerd Mullender 29 Jan '15

29 Jan '15

The MonetDB team at CWI/MonetDB BV is pleased to announce the Oct2014-SP2 bugfix release of the MonetDB suite of programs. More information about MonetDB can be found on our website at <http://www.monetdb.org/>. For details on this release, please see the release notes at <http://www.monetdb.org/Downloads/ReleaseNotes>. As usual, the download location is <http://dev.monetdb.org/downloads/>. Oct 2014-SP2 bugfix release Client Package * Changes to the Perl interface, thanks to Stefan O'Rear: 1. removes "use sigtrap", because this has global effects and should not be used by modules, only by the application. 2. allows Perl 5.8.1+ Unicode strings to be passed to quote() and included in statements (UTF-8 encoded, as expected by Monet's str module) 3. quote and unquote now use the same quoting rules as the MonetDB server, allowing for all characters except NUL to be round-tripped 4. several character loops have been reimplemented in regex for much greater performance 5. micro-optimizations to the result fetch loop 6. block boundaries are preserved in piggyback data so that Xclose is not appended or prepended to a SQL command 7. diagnostic messages #foo before a result header are ignored, this is necessary to use recycler_pipe 8. fail quickly and loudly if we receive a continuation prompt (or any other response that starts with a non-ASCII character) 9. header lines must start with %, not merely contain %, fixing a bug when querying a table where string values contain % 10. after closing a large resultset, account for the fact that a reply will come and do not lose sync 11. allow a MAPI_TRACE environment variable to dump wire protocol frames to standard output 12. fixes maximum MAPI block size to match the server limit of 16k. previously would crash on blocks larger than 16k SQL * Fixed a typo in a column name of the sys.tablestoragemodel view (auxillary changed to auxiliary). Bug Fixes * 3467: Field aliases with '#' character excise field names in result set. * 3605: relational query without result * 3619: Missing dll on MonetDB Start * 3622: Type resolution error * 3624: insert of incomplete or invalid ip address values in inet column is silently accepted but the values are not stored (they become/show nil) * 3626: casting a type without alias results in program contains errors * 3628: mclient and ODBC driver report 'type mismatch' when stddev_pop used in a select which returns 0 rows * 3629: IF THEN ELSEIF always evaluates the first test as true * 3630: segv on rel_order_by_column_exp * 3632: running make clean twice gives an error in clients/ruby/adapter * 3633: Wrong result for HAVING with floating-point constant * 3640: Missing implementation of scalar function: sql_sub(<date>, <month interval>) * 3641: SQL lexer fails to detect string end if it the last character is U+FEFF ZERO WIDTH NO-BREAK SPACE * 3642: Combined WHERE conditions less-than plus equals-to produce incorrect results * 3643: Missing implementations of scalar function: sql_sub(<timetz>, arg2) * 3644: COPY INTO fails to import "inet" data type when value has prefix length in CIDR notation * 3646: ORDER BY clause does not produce proper results on 'inet' datatype * 3649: recycler crashes with concurrent transactions

1 0

Re: MonetDB: Oct2014 - Reimplemented rangejoin, now using imprints o...
by Roberto Cornacchia 28 Jan '15

28 Jan '15

Hi Sjoerd, This is very welcome! If I read well, this will use, in order of preference: 1. binary search (if l is sorted) 2. imprints if available 3. nested loop otherwise We use rangejoin extensively within Spinque and the previous one (just nested loop) has never an option. So far we have been using our own version which is perhaps naive but proved to be effective: - sort all inputs if not sorted already - perform a mergejon-like fast scan Though simple, I have not found so far a case where this strategy would not outperform nested loops by far. The cost of sorting was always far less than the cost of a full nested loop. I was wondering what your thoughts about this would be. Could this strategy replace the number 3 above? Does it make sense to keep the vanilla nested loop? Best, Roberto On 27 January 2015 at 14:09, Sjoerd Mullender <commits(a)monetdb.org> wrote: > Changeset: 5147add3bb38 for MonetDB > URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=5147add3bb38 > Modified Files: > gdk/ChangeLog.Oct2014 > gdk/gdk_join.c > gdk/gdk_private.h > gdk/gdk_select.c > Branch: Oct2014 > Log Message: > > Reimplemented rangejoin, now using imprints or binary search if possible. > >

2 2

Re: MonetDB: Oct2014 - Use imprints to calculate min/max of bat.
by Lefteris 22 Jan '15

22 Jan '15

Imprints code contains such a search for min/max, maybe we could expose it as a function and thus avoid having the same code twice (for the usual reasons of easy maintenance and bugfix) On Thu, Jan 22, 2015 at 2:15 PM, Sjoerd Mullender <commits(a)monetdb.org> wrote: > Changeset: 69a4f7d2fd58 for MonetDB > URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=69a4f7d2fd58 > Modified Files: > gdk/gdk_aggr.c > Branch: Oct2014 > Log Message: > > Use imprints to calculate min/max of bat. > But with limitations: no grouped min/max, and only on the complete bat > (i.e. if view, it's a view on the complete bat). > > > diffs (37 lines): > > diff --git a/gdk/gdk_aggr.c b/gdk/gdk_aggr.c > --- a/gdk/gdk_aggr.c > +++ b/gdk/gdk_aggr.c > @@ -2207,8 +2207,31 @@ BATminmax(BAT *b, void *aggr, > return NULL; > needdecref = 1; > } > - (void) (*minmax)(&pos, b, NULL, 1, 0, 0, 0, BATcount(b), > - NULL, NULL, BATcount(b), 1, 0); > + if (b->T->imprints && > + (VIEWtparent(b) == 0 || > + BATcount(b) == BATcount(BBPdescriptor(VIEWtparent(b))))) { > + pos = oid_nil; > + if (minmax == do_groupmin) { > + /* find first non-empty bin */ > + for (s = 0; s < b->T->imprints->bits; s++) { > + if (b->T->imprints->stats[s + 128]) { > + pos = b->T->imprints->stats[s] + > b->hseqbase; > + break; > + } > + } > + } else { > + /* find last non-empty bin */ > + for (s = b->T->imprints->bits - 1; s >= 0; s--) { > + if (b->T->imprints->stats[s + 128]) { > + pos = b->T->imprints->stats[s + > 64] + b->hseqbase; > + break; > + } > + } > + } > + } else { > + (void) (*minmax)(&pos, b, NULL, 1, 0, 0, 0, BATcount(b), > + NULL, NULL, BATcount(b), 1, 0); > + } > if (pos == oid_nil) { > res = ATOMnilptr(b->ttype); > } else { > _______________________________________________ > checkin-list mailing list > checkin-list(a)monetdb.org > https://www.monetdb.org/mailman/listinfo/checkin-list >

1 1

Perl driver improvements
by Stefan O'Rear 13 Jan '15

13 Jan '15

I have been integrating MonetDB into a Perl system for the last few months, and have made a few changes to the Perl driver to improve performance and reliability. I hereby release these changes under the same license as MonetDB itself. I have permission from my employer to do this. Is this to correct place to send and/or discuss patches? In order of patch hunks; more details will be provided for any part if requested: 1. removes "use sigtrap", because this has global effects and should not be used by modules, only by the application. 2. allows Perl 5.8.1+ Unicode strings to be passed to quote() and included in statements (UTF-8 encoded, as expected by Monet's str module) 3. quote and unquote now use the same quoting rules as the MonetDB server, allowing for all characters except NUL to be round-tripped 4. several character loops have been reimplemented in regex for much greater performance 5. micro-optimizations to the result fetch loop 6. block boundaries are preserved in piggyback data so that Xclose is not appended or prepended to a SQL command 7. diagnostic messages #foo before a result header are ignored, this is necessary to use recycler_pipe 8. fail quickly and loudly if we receive a continuation prompt (or any other response that starts with a non-ASCII character) 9. header lines must start with %, not merely contain %, fixing a bug when querying a table where string values contain % 10. after closing a large resultset, account for the fact that a reply will come and do not lose sync 11. allow a MAPI_TRACE environment variable to dump wire protocol frames to standard output 12. fixes maximum MAPI block size to match the server limit of 16k. previously would crash on blocks larger than 16k

2 1

Bug in Query Plan??
by Vijay Krishna 09 Jan '15

09 Jan '15

Hi, I have been studying on multiple join performances in MonetDB. I am running the latest version of MonetDB Oct2014 - SP1 (MonetDB-11.19.3) on a CentOS machine with 128 GB RAM. All my tables have 100 to 300 columns and around 35 lakh rows. I have the following query with 5 joins and a few criteria. SELECT * FROM table1 INNER JOIN table2 on table1.col1=table2.col1 LEFT JOIN table3 ON table1.col2=table3.col1 LEFT JOIN table3 ON table1.col3=table3.col1 LEFT JOIN table4 ON table2.col2=table4.col1 LEFT JOIN table5 ON table2.col3=table5.col1 WHERE (((table2.col10 like '%a%' OR table5.col11 like '%s%') and ((table2.col12 like '%w%' *and table4.col13 like '%k'*) OR table2.col14 like '%h%') and (table1.col21 = 1) AND (table1.col22 IN (1,2,3))) AND (((table1.col23 >= 15842000000000000) AND (table1.col23 <= 15842999999999999)) OR ((table1.col23 >= 0) AND (table1.col23 <= 999999999999)))) ORDER BY 1 DESC LIMIT 10 OFFSET 0; This query has only 2000 matching rows and it returns the result in *2.9 seconds*. In the same query, if I remove one criteria alone (the bolded part), the result would still match only 2000 rows, but the result would come in *300 milliseconds* even. I used the PLAN statement and found that the query plan that the executor takes for the first query is a much complex path and *it performs 3 joins for all the 35 lakh rows* (i.e. before executing the criteria even) and hence the time has spiked to 3 seconds. May be the hot data of 35 lakh tuples would have not fit into the memory?? Not sure. But if I remove the highlighted criteria, the executor evaluates the criteria first and *it performs the joins for only 7000 rows* and hence it is faster 1000 times. Is this a bug? Or is there any way to optimise my query to match the executor to choose the right plan? *I am looking on to install MonetDB in our production setup, but finding this as a showstopper. * Any help much appreciated. I could even demo this. Thanks & Regards, Vijayakrishna.P. Mobile : (+91) 9500402305.

1 0