Message-ID: <4DE7C90E.3020408@peak6.com>
Date: Thu, 2 Jun 2011 12:31:58 -0500
From: Shaun Thomas <sthomas@peak6.com>
Reply-To: <sthomas@peak6.com>
Organization: Peak6
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.9.2.7) Gecko/20100720 Lightning/1.0b2 Thunderbird/3.1.1
MIME-Version: 1.0
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>
CC: CS DBA <cs_dba@consistentstate.com>, <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>
In-Reply-To: <4DE770E6020000250003E01C@gw.wicourts.gov>
Content-Type: text/plain; charset="ISO-8859-1"; format=flowed
content-transfer-encoding: 7bit

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@peak6.com

______________________________________________

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