Message-ID: <5037A9C5.4030701@optionshouse.com>
Date: Fri, 24 Aug 2012 11:20:21 -0500
From: Shaun Thomas <sthomas@optionshouse.com>
Reply-To: <sthomas@optionshouse.com>
Organization: Peak6
User-Agent: Mozilla/5.0 (X11; Linux i686;
	rv:14.0) Gecko/20120714 Thunderbird/14.0
MIME-Version: 1.0
To: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Loose Index Scans by Planner?
Content-Type: text/plain; charset="ISO-8859-1"; format=flowed
content-transfer-encoding: 7bit

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