Message-ID: <4DE7E1C1.9040300@consistentstate.com>
Date: Thu, 02 Jun 2011 13:17:21 -0600
From: CS DBA <cs_dba@consistentstate.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.9.2.17) Gecko/20110428 Fedora/3.1.10-1.fc14 Lightning/1.0b2
	Thunderbird/3.1.10
MIME-Version: 1.0
To: sthomas@peak6.com
CC: Kevin Grittner <Kevin.Grittner@wicourts.gov>,
	pgsql-performance@postgresql.org
Subject: Re: Problem query
References: <4DE69D92.5040407@consistentstate.com>
	<4DE66AF8020000250003DFB6@gw.wicourts.gov>
	<4DE6BD11.6060603@consistentstate.com>
	<4DE74E06020000250003DFE6@gw.wicourts.gov>
	<4DE7AD6E.3020604@peak6.com>
	<4DE768E9020000250003E002@gw.wicourts.gov>
	<4DE770E6020000250003E01C@gw.wicourts.gov>
	<4DE7C90E.3020408@peak6.com>
In-Reply-To: <4DE7C90E.3020408@peak6.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 06/02/2011 11:31 AM, Shaun Thomas wrote:
> On 06/02/2011 11:15 AM, Kevin Grittner wrote:
>
>> They all gave the same result, of course, and they all used a seq
>> scan..
>
> And they all will. I created a test table with a bunch of 
> generate_series and emulated 200 unique matches of column1 and 
> column2, on a table with a mere 1-million rows (5000 for each of 
> column3). And no matter what index combination I used, it always did a 
> sequence scan... even when I indexed every column and indexed column3 
> descending.
>
> But here's the thing. I turned off sequence scans to force index 
> scans, and it got 2-3x slower. But is that really surprising? Without 
> a proper where exclusion, it has to probe every occurrence... also 
> known as a loose index scan, which PostgreSQL doesn't have (yet).
>
> And... this is horrifying, but:
>
> WITH RECURSIVE t1 AS (
>   SELECT min(f.tds_cx_ind) AS tds_cx_ind
>     FROM max_xtrv_st_t f
>    UNION ALL
>   SELECT (SELECT min(tds_cx_ind)
>             FROM max_xtrv_st_t f
>            WHERE f.tds_cx_ind > t1.tds_cx_ind)
>     FROM t1
>    WHERE t1.tds_cx_ind IS NOT NULL
> ), t2 AS (
>   SELECT min(f.cxs_ind_2) AS cxs_ind_2
>     FROM max_xtrv_st_t f
>    UNION ALL
>   SELECT (SELECT min(cxs_ind_2)
>             FROM max_xtrv_st_t f
>            WHERE f.cxs_ind_2 > t2.cxs_ind_2)
>     FROM t2
>    WHERE t2.cxs_ind_2 IS NOT NULL
> )
> SELECT t1.tds_cx_ind, t2.cxs_ind_2  FROM t1, t2
>  WHERE t1.tds_cx_ind IS NOT NULL
>    AND t2.cxs_ind_2 IS NOT NULL;
>
> It works on my test, but might not be what OP wants. It's a cross 
> product of the two unique column sets, and it's possible it represents 
> combinations that don't exist. But I suppose a late EXISTS pass could 
> solve that problem.
>
> I assume there's an easier way to do that. In either case, when is PG 
> getting loose index scans? ;)
>


Thanks everyone for the feedback.  I'll attempt the suggestions from 
today as soon as I can and let you know where we end up.


-- 
---------------------------------------------
Kevin Kempter       -       Constent State
A PostgreSQL Professional Services Company
           www.consistentstate.com
---------------------------------------------