Обсуждение: best index for timestamp field null and not null queries
Hi, I've got a database table with a datetime column "deleted_at". I'll be running lots of queries against the table but they'll all be of the nature "deleted_at IS NULL" and "deleted_at IS NOT NULL".
What's the best index to place on this column? I assume if I add just a straight index then it'll index all of the values that go into it.
On 26/08/12 19:08, Jeremy Wells wrote:
Hi, I've got a database table with a datetime column "deleted_at". I'll be running lots of queries against the table but they'll all be of the nature "deleted_at IS NULL" and "deleted_at IS NOT NULL".What's the best index to place on this column? I assume if I add just a straight index then it'll index all of the values that go into it.
Not tested! How about the following partial index? CREATE INDEX deleted_at_is_null ON my_table WHERE deleted_at IS NULL; SELECT count(*) FROM my_table WHERE deleted_at IS NULL; The index should be substantially smaller than one storing dates, as the field will be small and you will have fewer rows. If the NULL value is the most common, then use the predicate deleted_at IS NOT NULL (and change the index name accordingly!). Cheers, Gavin
Hi
Well I do not pretend to give an answer, it is rather a question -I am a novice in postgresql two!-
Why not to split the table in two, the second ones inherits the first. Your datetime column is present only in the daughter. Parent table are undeleted, daughter one are deleted. A trigger on the parent table fils the daughter one for DELETE queries, and you can make your queries on the parent one with SELECT ONLY or SELECT depending on your need to have or not to have the deleted rows
Hopefully, expert will provide us with the advantages and drawbacks of both solutions!
Hopefully, expert will provide us with the advantages and drawbacks of both solutions!
Romain
Date: Sun, 26 Aug 2012 19:08:36 +1200
From: jemmyw@gmail.com
To: pgsql-novice@postgresql.org
Subject: [NOVICE] best index for timestamp field null and not null queries
Date: Sun, 26 Aug 2012 19:08:36 +1200
From: jemmyw@gmail.com
To: pgsql-novice@postgresql.org
Subject: [NOVICE] best index for timestamp field null and not null queries
Hi, I've got a database table with a datetime column "deleted_at". I'll be running lots of queries against the table but they'll all be of the nature "deleted_at IS NULL" and "deleted_at IS NOT NULL".
What's the best index to place on this column? I assume if I add just a straight index then it'll index all of the values that go into it.