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

> "Bryan White" <bryan@arcamax.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.