CREATE TABLE foo ( id TEXT NOT NULL, bar INTEGER NOT NULL DEFAULT 0, PRIMARY KEY (id) ); CREATE INDEX foo_bar_idx ON foo(bar) WHERE NOT bar = 0;
I can sort on the
bar column in DESC order about 3 times faster than I can sort on the
bar column in ASC order. I assume an index on the column isn't helping with the sort since, relatively speaking w/ millions of rows, most of the values are the same.
The default value for the
bar column is 0, but I only care about rows that have a value between 1 and 9, so my query includes a where clause for that. Not sure if the partial index is helping.
SELECT id FROM foo WHERE bar > 0 ORDER BY bar DESC; SELECT id FROM foo WHERE bar > 0 ORDER BY bar ASC;
Across 9M rows, explain analize shows the ORDER BY DESC taking less than a second, and ORDER BY ASC taking a little over 3 seconds. Approximately 117K of the 9M rows get filtered by the WHERE clause.
Is the ORDER BY DESC faster because it doesn't necessarily have to worry about the 0 values? Any way to speed up the ORDER BY ASC query? Or… should I just be happy w/ a 3 second query time for a table w/ 9M rows in it? I'm continually adding rows, so I'm worried about the query time increasing over time…