Re: Problem query

Shaun Thomas <sthomas(at)peak6(dot)com> wrote:
> 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 always eschew generalizations, since they're always wrong. ;-) I
used a real table which had somewhat similar indexes to what I think
the OP is using, and tried the fastest query using the sequential
scan. A typical result once cached:

explain analyze select count(*) from
(select distinct "caseType", "statusCode" from "Case") x;

Aggregate (cost=10105.01..10105.02 rows=1 width=0)
(actual time=478.893..478.893 rows=1 loops=1)
-> HashAggregate (cost=10101.95..10103.31 rows=136 width=6)
(actual time=478.861..478.881 rows=79 loops=1)
-> Seq Scan on "Case"
(cost=0.00..7419.20 rows=536550 width=6)
(actual time=0.010..316.481 rows=536550 loops=1)
Total runtime: 478.940 ms

Then I tried it with a setting designed to discourage seq scans.
A typical run:

set cpu_tuple_cost = 1;
explain analyze select count(*) from
(select distinct "caseType", "statusCode" from "Case") x;

Aggregate (cost=544529.30..544530.30 rows=1 width=0)
(actual time=443.972..443.972 rows=1 loops=1)
-> Unique (cost=0.00..544392.95 rows=136 width=6)
(actual time=0.021..443.933 rows=79 loops=1)
-> Index Scan using "Case_CaseTypeStatus" on "Case"
(cost=0.00..541710.20 rows=536550 width=6)
(actual time=0.019..347.193 rows=536550 loops=1)
Total runtime: 444.014 ms

Now, on a table which didn't fit in cache, this would probably be
another story....

-Kevin