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

> "Bryan White" <bryan(at)arcamax(dot)com> writes:
> > Top tells me the front end process is using 5 to 10 percent of the CPU
and
> > the back end is using 10 to 20 percent. The load average is about 1.0
and
> > the CPU is about 80% idle.
>
> It's probably waiting for disk I/O ...
>
> 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.
Here are the index creates from a dump:
CREATE INDEX "icusln" on "customer" using btree ( "lname" "text_ops" );
CREATE UNIQUE INDEX "icusem2" on "customer" using btree ( "email"
"text_ops" );
CREATE INDEX "icusph" on "customer" using btree ( "phone" "text_ops" );
CREATE UNIQUE INDEX "icusid" on "customer" using btree ( "custid"
"int4_ops" );

CREATE INDEX "iordldsrc3" on "orders" using btree ( "leadsource"
"text_ops" );
CREATE UNIQUE INDEX "iordid3" on "orders" using btree ( "orderid"
"int4_ops" );
CREATE INDEX "iordcus3" on "orders" using btree ( "custid" "int4_ops",
"date" "date_ops" );
CREATE INDEX "iorddate3" on "orders" using btree ( "date" "date_ops" );

The iordcus3 index has a second component that is irrelevent to this
operation. Is the optimizer intelligent enough to still use it.