Re: Problem query

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(at)peak6(dot)com

______________________________________________

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