From: | "Bryan White" <bryan(at)arcamax(dot)com> |
---|---|
To: | "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | "pgsql-general" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: PG 7.0 is 2.5 times slower running a big report |
Date: | 2000-05-25 02:09:33 |
Message-ID: | 001301bfc5ee$456b2000$0200a8c0@nwptn1.va.home.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
> "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.
From | Date | Subject | |
---|---|---|---|
Next Message | Lamar Owen | 2000-05-25 02:14:37 | Re: Postgres Instability |
Previous Message | Jan Wieck | 2000-05-25 02:05:10 | Re: Migrating from mysql. |