From: Shaun Thomas <sthomas@peak6.com>
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>
Cc: CS DBA <cs_dba@consistentstate.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Problem query
Date: Thu, 2 Jun 2011 10:34:06 -0500
Message-ID: <4DE7AD6E.3020604@peak6.com> (raw)
In-Reply-To: <4DE74E06020000250003DFE6@gw.wicourts.gov>
References: <4DE69D92.5040407@consistentstate.com>
<4DE66AF8020000250003DFB6@gw.wicourts.gov>
<4DE6BD11.6060603@consistentstate.com>
<4DE74E06020000250003DFE6@gw.wicourts.gov>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: sthomas@peak6.com, Kevin.Grittner@wicourts.gov, cs_dba@consistentstate.com
Subject: Re: Problem query
In-Reply-To: <4DE7AD6E.3020604@peak6.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox