On Thu, Jun 8, 2017 at 11:05 AM, Jeremy Finzel <finzelj@xxxxxxxxx> wrote: > On Thu, Jun 8, 2017 at 9:58 AM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote: >> Merlin Moncure <mmoncure@xxxxxxxxx> writes: >> > Having said that, what I typically do in such >> > cases (this comes a lot in database driven work queues) something like >> > this: >> > CREATE INDEX ON table (OrderCol) WHERE col IS NOT NULL; >> >> Right, you can frequently get a lot of mileage out of indexing something >> that's unrelated to the predicate condition, but is also needed by the >> query you want to optimize. > Normally, I find that in these situations, it makes sense to index the > primary key of the table WHERE col is not null, because it will usually > cover the largest number of cases, and is much better than a two-value > boolean index, for example. [meta note: please try to avoid top-posting] Yeah, if you index the primary key and query it like this: CREATE INDEX ON table (pkey) WHERE col IS NOT NULL; SELECT pkey FROM table WHERE col IS NOT NULL ORDER BY pkey LIMIT n; This can give the best possible results since this can qualify for an index only scan :-). merlin -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
