Re: PG 7.0 is 2.5 times slower running a big report

"Bryan White" <bryan(at)arcamax(dot)com> writes:
>> What does EXPLAIN tell you about how the queries are being executed?
>> Do you by any chance have the 6.5.3 system still available to compare
>> its EXPLAIN output?

> explain select custid, poregdate, firstcontactdate,
> mastersubscribed, offersubscribed, bouncecount
> from customer order by custid;
> Sort (cost=598354.56..598354.56 rows=2446621 width=40)
> -> Seq Scan on customer (cost=0.00..75939.21 rows=2446621 width=40)

> explain select custid, orderid, date, leadsource,
> paymentstatus, shipping + tax
> from orders order by custid;
> Sort (cost=167945.80..167945.80 rows=588242 width=60)
> -> Seq Scan on orders (cost=0.00..31399.42 rows=588242 width=60)

> explain select custid, action, offer, date, source
> from contact order by custid;
> Index Scan using iconcus4 on contact (cost=0.00..1446338.62 rows=6462635
> width=44)

> explain select custid, listid
> from custlist order by custid;
> Index Scan using iclcust3 on custlist (cost=0.00..334501.73 rows=2738543
> width=8)

> I find the 'Sort's on customer and orders supprising.

Well, in fact you should not, because 6.5.* didn't know how to use an
index to implement ORDER BY at all ;-). 7.0 knows how to do it both
ways, and so the real question here is whether the planner's cost
estimates for the two ways are accurate or not.

My guess is that the index-based ORDER BYs are actually a lot slower
than sort-based for your tables, and that's why 7.0 is slower than 6.5.
But the planner thinks they will be faster in two of these cases.
We need to figure out why it's making the wrong choice.

You can force sort-based or index-based ordering by issuing
SET enable_indexscan = OFF;
or
SET enable_sort = OFF;
respectively. I suggest that the next step should be to see what
EXPLAIN says for all four queries in both cases (so we can see what
the planner's estimates for the two cases actually are for each
table), and then to measure the actual runtimes of each of the
SELECTs both ways.

regards, tom lane