Maybe I should post this in Hackers instead, but I figured I'd start
here to avoid cluttering up that list.
So, we know we have a way of doing a loose index scan with CTEs:
http://wiki.postgresql.org/wiki/Loose_indexscan
But that got me wondering. The planner knows from pg_stats that col1
could have low cardinality. If that's the case, and a WHERE clause uses
a two column index, and col2 is specified, why can't it walk each
individual bucket in the two-column index, and use col2? So I forced
such a beast with a CTE:
WITH RECURSIVE t AS (
SELECT min(col1) AS col1
FROM tablename
UNION ALL
SELECT (SELECT min(col1)
FROM tablename
WHERE col1 > t.col1)
FROM t
WHERE t.col1 IS NOT NULL
)
SELECT p.*
FROM t
JOIN tablename p USING (col1)
where p.col2 = 12345
I ask, because while the long-term fix would be to re-order the index to
(col2, col1), this seems like a situation the planner could easily
detect and compensate for. In our particular example, execution time
went from 160ms to 2ms with the CTE rewrite. This is a contrived
example, but it seems like loose index scans would be useful in other
ways. Heck, this:
SELECT DISTINCT col1
FROM tablename;
Has terrible performance because it always seems to revert to a sequence
scan, but it's something people do *all the time*. I can't reasonably
expect all of my devs to switch to that admittedly gross CTE to get a
faster effect, so I'm just thinking out loud.
Until PG puts in something to fix this, I plan on writing a stored
procedure that writes a dynamic CTE and returns a corresponding result
set. It's not ideal, but it would solve our particular itch. Really,
this should be possible with any indexed column, so I might abstract it.
--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604
312-444-8534
sthomas@optionshouse.com
______________________________________________
See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email