Re: Loose Index Scans by Planner?

On Fri, Aug 24, 2012 at 9:20 AM, Shaun Thomas <sthomas(at)optionshouse(dot)com> wrote:
> 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

That is awesome. I had never though of trying to do it that way.

> I ask, because while the long-term fix would be to re-order the index to
> (col2, col1),

Not always. The case for having (col1,col2) might be very compelling.
And having to maintain both orderings when just maintaining one would
be "good enough" would kind of suck. Having the planner do the best
it can given the index it has is a good thing.

I would also note that having this feature (called "skip scan" in some
other products) would mimic what happens when you need to do a query
specifying col2 but not col1 on a table family which is list
partitioned on col1. Getting some of the benefits of partitioning
without having to actually do the partitioning would be a good thing.

> this seems like a situation the planner could easily detect
> and compensate for.

Yes, it is just a Small Matter Of Programming :)

And one I've wanted for a while.

If only someone else would offer to do it for me....

Cheers,

Jeff