Re: Problem query

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? ;)

--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
sthomas(at)peak6(dot)com

______________________________________________

See http://www.peak6.com/email_disclaimer.php
for terms and conditions related to this email