Re: poor performance of db?
От | SpaceBallOne |
---|---|
Тема | Re: poor performance of db? |
Дата | |
Msg-id | BAY14-DAV1047A9E907F6FDED2434DACC860@phx.gbl обсуждение исходный текст |
Ответ на | poor performance of db? ("SpaceBallOne" <space_ball_one@hotmail.com>) |
Список | pgsql-performance |
Thanks for the replies guys, Chris - very cool feature timing - didnt know about that one. Appears to be taking the following times in pulling up the page: web browser: 1.15 sec postgres: 1.52 sec other: 0.83 sec Andrew: Query looks like the following: explain analyse SELECT job.*, customer.*, ubd.suburb, location.*, street.street, location.designation_no, a1.initials as surveyor, a2.initials as draftor, prices.*, plans.* FROM job, login a1, login a2, prices, location, ubd, plans WHERE ( a1.code = job.surveyor_no AND a2.code = job.draftor_no AND job.customer_no = customer.customer_no AND job.location_no = location.location_no AND location.suburb_no = ubd.suburb_id AND location.street_no = street.street_no AND job.customer_no = customer.customer_no AND job.price_id = prices.pricelist_id AND job.price_revision = prices.revision AND location.plan_no = plans.number AND location.plan_type = plans.plantype AND ( (job.jobbookflag <> 'flagged') AND ( job.status = 'normal' ) )) ORDER BY job_no DESC; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------- Sort (cost=566.31..567.06 rows=298 width=2626) (actual time=1378.38..1380.08 rows=353 loops=1) Sort Key: job.job_no -> Hash Join (cost=232.59..554.06 rows=298 width=2626) (actual time=124.96..1374.12 rows=353 loops=1) Hash Cond: ("outer".suburb_no = "inner".suburb_id) -> Hash Join (cost=221.45..519.06 rows=288 width=2606) (actual time=118.60..1187.87 rows=353 loops=1) Hash Cond: ("outer".street_no = "inner".street_no) -> Hash Join (cost=204.79..496.64 rows=287 width=2587) (actual time=108.16..997.57 rows=353 loops=1) Hash Cond: ("outer".surveyor_no = "inner".code) -> Hash Join (cost=203.21..490.05 rows=287 width=2573) (actual time=106.89..823.47 rows=353 loops=1) Hash Cond: ("outer".customer_no = "inner".customer_no) -> Hash Join (cost=159.12..440.93 rows=287 width=2291) (actual time=92.16..654.51 rows=353 loops=1) Hash Cond: ("outer".draftor_no = "inner".code) -> Hash Join (cost=157.55..434.33 rows=287 width=2277) (actual time=90.96..507.34 rows=353 loops=1) Hash Cond: ("outer".price_id = "inner".pricelist_id) Join Filter: ("outer".price_revision = "inner".revision) -> Hash Join (cost=142.95..401.01 rows=336 width=2150) (actual time=82.57..377.87 rows=353 loops=1) Hash Cond: ("outer".plan_no = "inner".number) Join Filter: ("outer".plan_type = "inner".plantype) -> Hash Join (cost=25.66..272.20 rows=418 width=2110) (actual time=14.58..198.50 rows=353 loops=1) Hash Cond: ("outer".location_no = "inner".location_no) -> Seq Scan on job (cost=0.00..238.18 rows=418 width=2029) (actual time=0.31..95.21 rows=353 loops=1) Filter: ((jobbookflag <> 'flagged'::character varying) AND (status = 'normal'::character varying)) -> Hash (cost=23.53..23.53 rows=853 width=81) (actual time=13.91..13.91 rows=0 loops=1) -> Seq Scan on "location" (cost=0.00..23.53 rows=853 width=81) (actual time=0.03..8.92 rows=853 loops=1) -> Hash (cost=103.43..103.43 rows=5543 width=40) (actual time=67.55..67.55 rows=0 loops=1) -> Seq Scan on plans (cost=0.00..103.43 rows=5543 width=40) (actual time=0.01..36.89 rows=5544 loops=1) -> Hash (cost=13.68..13.68 rows=368 width=127) (actual time=7.98..7.98 rows=0 loops=1) -> Seq Scan on prices (cost=0.00..13.68 rows=368 width=127) (actual time=0.03..5.83 rows=368 loops=1) -> Hash (cost=1.46..1.46 rows=46 width=14) (actual time=0.57..0.57 rows=0 loops=1) -> Seq Scan on login a2 (cost=0.00..1.46 rows=46 width=14) (actual time=0.02..0.31 rows=46 loops=1) -> Hash (cost=42.07..42.07 rows=807 width=282) (actual time=14.24..14.24 rows=0 loops=1) -> Seq Scan on customer (cost=0.00..42.07 rows=807 width=282) (actual time=0.03..9.03 rows=807 loops=1) -> Hash (cost=1.46..1.46 rows=46 width=14) (actual time=0.57..0.57 rows=0 loops=1) -> Seq Scan on login a1 (cost=0.00..1.46 rows=46 width=14) (actual time=0.02..0.31 rows=46 loops=1) -> Hash (cost=14.53..14.53 rows=853 width=19) (actual time=9.79..9.79 rows=0 loops=1) -> Seq Scan on street (cost=0.00..14.53 rows=853 width=19) (actual time=0.01..5.12 rows=853 loops=1) -> Hash (cost=9.91..9.91 rows=491 width=20) (actual time=5.73..5.73 rows=0 loops=1) -> Seq Scan on ubd (cost=0.00..9.91 rows=491 width=20) (actual time=0.02..2.98 rows=491 loops=1) Total runtime: 1383.99 msec (39 rows) Time: 1445.80 ms I tried setting up 10-15 indexes yesterday, but couldn't see they were doing anything. I have since deleted them (on the premise that I didn't have a clue what I was doing). I'm not actually running any keys in this database... would that be a simpler way of running my queries? I only learnt postgres / unix from scratch a year ago so my db setup and queries is probably pretty messy :) Thanks, Dave space_ball_one@hotmail.com ----- Original Message ----- From: "Andrei Reinus" <andrei.reinus@uptime.ee> To: "SpaceBallOne" <space_ball_one@hotmail.com> Cc: <pgsql-performance@postgresql.org> Sent: Monday, January 24, 2005 5:22 PM Subject: Re: [PERFORM] poor performance of db? > SpaceBallOne wrote: > >> Hello everyone, >> >> First time poster to the mailing list here. >> >> We have been running pgsql for about a year now at a pretty basic >> level (I guess) as a backend for custom >> web (intranet) application software. Our database so far is a "huge" >> (note sarcasm) 10 Mb containing of about 6 or so principle tables. >> >> Our 'test' screen we've been using loads a 600kb HTML document which >> is basically a summary of our client's orders. It took originally 11.5 >> seconds to load in internet explorer (all 10.99 seconds were pretty >> much taken up by postgres processes on a freebsd server). >> >> I then re-wrote the page to use a single select query to call all the >> information needed by PHP to draw the screen. That managed to shave it >> down to 3.5 seconds... but this so far is as fast as I can get the >> page to load. Have tried vacuuming and creating indexes but to no >> avail. (increasing shared mem buffers yet to be done) >> >> Now heres the funny bit ... >> >> Every time I tested an idea to speed it up, I got exactly the same >> loading time on a Athlon 1800+, 256Mb RAM, 20Gb PATA computer as >> compared to a Dual Opteron 246, 1Gb RAM, 70Gb WD Raptor SATA server. >> Now, why a dual opteron machine can't perform any faster than a lowly >> 1800+ athlon in numerous tests is completely beyond me .. increased >> memory and RAID 0 disc configurations so far have not resulted in any >> significant performance gain in the opteron server. >> >> Do these facts sound right? If postgres is meant to be a 200Gb >> industrial strength database, should it really be taking this long >> pulling 600kb worth of info from a 10Mb database? And why no >> performance difference between two vastly different hardware spec'd >> computers??? Am I missing some vital postgres.conf setting?? >> >> Any advice welcome. >> >> Thanks, >> Dave >> space_ball_one@hotmail.com <mailto:space_ball_one@hotmail.com> >> > > Could you give us a bit more info. > What you are trying to do. EXPLAIN ANALYZE would be great. > In my experience first problem with the first db app is no indexes used > in joining. > > -- > -- Andrei Reinus > > -------------------------------------------------------------------------------- > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq >
В списке pgsql-performance по дате отправления: