Hi Meng,
thanks!
Imprints are made (and used) for inequality predicates (aka. "range selections"), only; hence, they are indeed not build (and would not help much) in you case of equality predicates (aka "point selections").
For quality predicates (aka "point selections"), MonetDB can exploit hash indexes or "ordered indexes". However, the former cost more than a scan to build; hence, they are not built with a single select --- MonetDB cannot know that more selects will come that might amortize the build costs ... Well, they once used to be build also with a single simple select directly on a base tables (persistent BAT), but that might either not be the case any more --- I haven't checked the code for some time --- or will not trigger due to "delta management" in the query plan.
While we (AFAIK) do not have a means to explicitly build a hash index, you can try to run a join query (with equality predicate) that involved this column --- preferably a self-join to make sure the column is indeed used as inner argument of a join --- to trigger the build of a hash index; e.g. something like select count(*) from id14 as a join id14 as b on a.id = b.id; Also, if your id column has unique values, make sure you declare it as PRIMARY KEY or UNIQUE; that might help as well. Be aware though, that, as opposed to imprints or ordered indexes, hash indexes are not persistent, i.e., they will not survive server restarts,and need to be recreated after each server restart ...
We do supposedly have a means to explicitly demand the build of ordered indexes --- something like "create ordered index ..." ---, but I cannot find the respective documentation right now. Sorry!
Maybe Lefteris or Martin know more and can help?
Best, Stefan
----- On Jul 20, 2017, at 3:11 AM, integrity 357416268@qq.com wrote:
Hi Stefan,
Nice talking to you again! Sorry my query in the image didn't show up, so I paste it in text now. The query is a simple select with equals. ======================================== sql>select * from id14 where id=1461; +------+-------+------+ | id | label | cid | +======+=======+======+ | 1461 | false | 0 | +------+-------+------+ 1 tuple ( 104.549ms ) sql> sql> sql>select row_number() over (),* from storage() where "table"='id14'; +------+--------+-------+--------+---------+----------+----------+---------+-----------+------------+----------+----------+-------+----------+--------+-----------+--------+----------+------+ | L5 | schema | table | column | type | mode | location | count | typewidth | | columnsize | heapsize | hashes | phash | imprints | sorted | revsorted | unique | | orderidx | L5 | +======+========+=======+========+=========+==========+==========+=========+===========+============+==========+==========+=======+==========+========+===========+========+==========+======+ | 1 | sys | id14 | id | bigint | writable | 12/1244 | 2811606 | 8 | 22492848 | 0 | | 28049448 | false | 0 | false | false | null | 0 | 1 | | 2 | sys | id14 | label | boolean | writable | 02/254 | 2811606 | 1 | 2811606 | 0 | | 0 | false | 0 | null | null | null | 0 | 2 | | 3 | sys | id14 | cid | bigint | writable | 12/1231 | 2811606 | 8 | 22492848 | 0 | | 0 | false | 0 | false | false | null | 0 | 3 | +------+--------+-------+--------+---------+----------+----------+---------+-----------+------------+----------+----------+-------+----------+--------+-----------+--------+----------+------+ 3 tuples (10.090ms)
===================================================== Best, Meng ------------------ Original ------------------ From: "Stefan Manegold";Stefan.Manegold@cwi.nl; Date: Jul 19, 2017 To: "Communication channel for MonetDB users"users-list@monetdb.org; Subject: Re: how to know imprints take effect?
Hi Meng,
most important question:
From what kind of queries do you expect millisecond responses over a table of 15,000,000 rows?
Thanks!
Best, Stefan
----- On Jul 19, 2017, at 12:37 PM, integrity 357416268@qq.com wrote:
Hi all,
I am back, and I am using MonetDB Dec2016-SP5 with "enable-optimize" to do graph computing. I remember that imprints are suitable for all numeric types, however, after querying on the bigint column for many times, I found there is no imprints.
What I want to achieve is to "make" MonetDB respond in milliseconds, although I know that in MonetDB it is not necessary to create index like imprints. So, on a table of 15,000,000 rows, can I expect to get response in milliseconds? and whether imprints will help with this?
Any comments is appreciated!
Best regards, Meng
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
-- | Stefan.Manegold@CWI.nl | DB Architectures (DA) | | www.CWI.nl/~manegold/ | Science Park 123 (L321) | | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) | _______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list