Re: Problem query

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
---------------------------------------------