Message-ID: <4DE7AD6E.3020604@peak6.com>
Date: Thu, 2 Jun 2011 10:34:06 -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>
In-Reply-To: <4DE74E06020000250003DFE6@gw.wicourts.gov>
Content-Type: text/plain; charset="ISO-8859-1"; format=flowed
content-transfer-encoding: 7bit

On 06/02/2011 08:47 AM, Kevin Grittner wrote:

> Is there some reason to believe that a sequential scan isn't the
> fastest way to get the data?  When generating summary data like
> this, it often is faster than lots of random access.  If you can
> coerce it into a faster plan by turning off enable_seqscan on the
> connection before running the query, then we can look at how you
> might adjust your costing parameters to get better plans.

This is right. There's really no way for the optimizer to get the values 
you want, even though your columns are indexed. But your query is a tad 
naive, unless you wrote up a special case for us. You're counting the 
number of maximum values in your table for tds_cx_ind and cxs_ind_2, but 
there will always be at least one for every combination. What you really 
want is this:

SELECT count(1) FROM (
   SELECT DISTINCT tds_cx_ind, cxs_ind_2
     FROM max_xtrv_st_t
);

If you really must have that inner query because it's generated and you 
won't know what it contains, you'd be better off with a CTE:

WITH x AS (
   SELECT max(pri_num)
     FROM max_xtrv_st_t
    GROUP BY tds_cx_ind, cxs_ind_2
)
SELECT count(1) FROM x;

You'll still get a sequence scan from these, however.

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