X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 4CB9040BFF2 for ; Sat, 29 Jan 2005 22:40:32 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 31301-07 for ; Sat, 29 Jan 2005 22:40:21 +0000 (GMT) Received: from fep7.cogeco.net (smtp.cogeco.net [216.221.81.25]) by svr1.postgresql.org (Postfix) with ESMTP id 735C040A3C6 for ; Sat, 29 Jan 2005 22:40:20 +0000 (GMT) Received: from windsortransportationclub.com (d57-59-156.home.cgocable.net [24.57.59.156]) by fep7.cogeco.net (Postfix) with SMTP id 1D826991 for ; Sat, 29 Jan 2005 17:40:12 -0500 (EST) Received: (qmail 26631 invoked from network); 29 Jan 2005 23:09:59 -0000 Received: from d36-66-205.home1.cgocable.net (HELO a96dfxb4kjzogw) (24.36.66.205) by 0 with SMTP; 29 Jan 2005 23:09:59 -0000 Message-ID: <001d01c50653$7e5d40a0$cd422418@a96dfxb4kjzogw> From: "Ken Egervari" To: References: <07FDEE0ED7455A48AC42AC2070EDFF7C3EE0B5@corpsrv2.tazznetworks.com> Subject: Re: Performance problem with semi-large tables Date: Sat, 29 Jan 2005 17:40:11 -0500 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_001A_01C50629.953FCF60" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.2180 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.157 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO, HTML_40_50, HTML_MESSAGE, RCVD_IN_NJABL_DUL, RCVD_IN_SORBS_DUL X-Spam-Level: * X-Archive-Number: 200501/615 X-Sequence-Number: 10305 This is a multi-part message in MIME format. ------=_NextPart_000_001A_01C50629.953FCF60 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Yes, I'm very well aware of VACUUM and VACUUM ANALYZE. I've even = clusted the date index and so on to ensure faster performance. ----- Original Message -----=20 From: David Parker=20 To: Ken Egervari ; pgsql-performance@postgresql.org=20 Sent: Saturday, January 29, 2005 5:04 PM Subject: Re: [PERFORM] Performance problem with semi-large tables You don't mention if you have run VACUUM or VACUUM ANALYZE lately. = That's generally one of the first things that folks will suggest. If you = have a lot of updates then VACUUM will clean up dead tuples; if you have = a lot of inserts then VACUUM ANALYZE will update statistics so that the = planner can make better decisions (as I understand it). Another data point people will ask for in helping you will be EXPLAIN = ANALYZE output from running the queries you think are slowing down. - DAP -------------------------------------------------------------------------= --- From: pgsql-performance-owner@postgresql.org = [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Ken = Egervari Sent: Wednesday, January 26, 2005 9:17 PM To: pgsql-performance@postgresql.org Subject: [PERFORM] Performance problem with semi-large tables Hi everyone. I'm new to this forum and was wondering if anyone would be kind = enough to help me out with a pretty severe performance issue. I believe = the problem to be rather generic, so I'll put it in generic terms. = Since I'm at home and not a work (but this is really bugging me), I = can't post any specifics. However, I think my explaination will = suffice. I have a 2 tables that are are getting large and will only get = larger with time (expoentially as more users sign on to the system). = Right the now, a table called 'shipment' contains about 16,000 rows and = 'shipment_status' contains about 32,500 rows. These aren't massive rows = (I keep reading about tables with millions), but they will definately = get into 6 digits by next year and query performance is quite poor. Now, from what I can understand about tuning, you want to specify = good filters, provide good indexes on the driving filter as well as any = referencial keys that are used while joining. This has helped me solve = performance problems many times in the past (for example, changing a = query speed from 2 seconds to 21 milliseconds). =20 However, I am now tuning queries that operate on these two tables = and the filters aren't very good (the best is a filter ratio of 0.125) = and the number of rows returned is very large (not taking into = consideration limits). For example, consider something like this query that takes ~1 second = to finish: select s.*, ss.* from shipment s, shipment_status ss, release_code r where s.current_status_id =3D ss.id and ss.release_code_id =3D r.id and r.filtered_column =3D '5' order by ss.date desc limit 100; Release code is just a very small table of 8 rows by looking at the = production data, hence the 0.125 filter ratio. However, the data = distribution is not normal since the filtered column actually pulls out = about 54% of the rows in shipment_status when it joins. Postgres seems = to be doing a sequencial scan to pull out all of these rows. Next, it = joins approx 17550 rows to shipment. Since this query has a limit, it = only returns the first 100, which seems like a waste. Now, for this query, I know I can filter out the date instead to = speed it up. For example, I can probably search for all the shipments = in the last 3 days instead of limiting it to 100. But since this isn't = a real production query, I only wanted to show it as an example since = many times I cannot do a filter by the date (and the sort may be date or = something else irrelavant). I'm just stressed out how I can make queries like this more = efficient since all I see is a bunch of hash joins and sequencial scans = taking all kinds of time. I guess here are my 2 questions: 1. Should I just change beg to change the requirements so that I can = make more specific queries and more screens to access those? 2. Can you recommend ways so that postgres acts on big tables more = efficiently? I'm not really interested in this specific case (I just = made it up). I'm more interested in general solutions to this general = problem of big table sizes with bad filters and where join orders don't = seem to help much. Thank you very much for your help. Best Regards, Ken Egervari ------=_NextPart_000_001A_01C50629.953FCF60 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Yes, I'm very well aware of VACUUM and = VACUUM=20 ANALYZE.  I've even clusted the date index and so on to ensure = faster=20 performance.
----- Original Message -----
From:=20 David=20 Parker
To: Ken Egervari ; pgsql-performance@postgr= esql.org=20
Sent: Saturday, January 29, = 2005 5:04=20 PM
Subject: Re: [PERFORM] = Performance=20 problem with semi-large tables

You don't mention if you have run VACUUM or = VACUUM=20 ANALYZE lately. That's generally one of the first things that folks = will=20 suggest. If you have a lot of updates then VACUUM will clean up dead = tuples;=20 if you have a lot of inserts then VACUUM ANALYZE will update = statistics so=20 that the planner can make better decisions (as I understand=20 it).
 
Another data point people will ask for in = helping you=20 will be EXPLAIN ANALYZE output from running the queries you think are = slowing=20 down.
 
- DAP


From: = pgsql-performance-owner@postgresql.org=20 [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of = Ken=20 Egervari
Sent: Wednesday, January 26, 2005 9:17 = PM
To:=20 pgsql-performance@postgresql.org
Subject: [PERFORM] = Performance=20 problem with semi-large tables

Hi everyone.
 
I'm new to this forum and was = wondering if=20 anyone would be kind enough to help me out with a pretty severe = performance=20 issue.  I believe the problem to be rather generic, so I'll put = it in=20 generic terms.  Since I'm at home and not a work (but this is = really=20 bugging me), I can't post any specifics.  However, I think my=20 explaination will suffice.
 
I have a 2 tables that are are = getting large=20 and will only get larger with time (expoentially as more users sign = on to=20 the system).  Right the now, a table called 'shipment' contains = about=20 16,000 rows and 'shipment_status' contains about 32,500 rows.  = These=20 aren't massive rows (I keep reading about tables with millions), but = they=20 will definately get into 6 digits by next year and query performance = is=20 quite poor.
 
Now, from what I can understand = about tuning,=20 you want to specify good filters, provide good indexes on the = driving filter=20 as well as any referencial keys that are used while joining.  = This has=20 helped me solve performance problems many times in the past (for = example,=20 changing a query speed from 2 seconds to 21 milliseconds). =20
 
However, I am now tuning queries = that operate=20 on these two tables and the filters aren't very good (the best is a = filter=20 ratio of 0.125) and the number of rows returned is very large (not = taking=20 into consideration limits).
 
For example, consider something = like this=20 query that takes ~1 second to finish:
 
select s.*, ss.*
from shipment s, shipment_status = ss,=20 release_code r
where s.current_status_id =3D = ss.id
   and ss.release_code_id = =3D=20 r.id
   and r.filtered_column = =3D=20 '5'
order by ss.date desc
limit 100;
 
Release code is just a very small = table of 8=20 rows by looking at the production data, hence the 0.125 filter = ratio. =20 However, the data distribution is not normal since the filtered = column=20 actually pulls out about 54% of the rows in shipment_status when it=20 joins.  Postgres seems to be doing a sequencial scan to pull = out all of=20 these rows.  Next, it joins approx 17550 rows to = shipment.  Since=20 this query has a limit, it only returns the first 100, which seems = like a=20 waste.
 
Now, for this query, I know I can = filter out=20 the date instead to speed it up.  For example, I can probably = search=20 for all the shipments in the last 3 days instead of limiting it to=20 100.  But since this isn't a real production query, I only = wanted to=20 show it as an example since many times I cannot do a filter by the = date (and=20 the sort may be date or something else irrelavant).
 
I'm just stressed out how I can = make queries=20 like this more efficient since all I see is a bunch of hash joins = and=20 sequencial scans taking all kinds of time.
 
I guess here are my 2 = questions:
 
1. Should I just change beg to = change the=20 requirements so that I can make more specific queries and more = screens to=20 access those?
2. Can you recommend ways so that = postgres acts=20 on big tables more efficiently?  I'm not really interested in = this=20 specific case (I just made it up).  I'm more interested in = general=20 solutions to this general problem of big table sizes with bad = filters and=20 where join orders don't seem to help much.
 
Thank you very much for your=20 help.
 
Best Regards,
Ken=20 Egervari
------=_NextPart_000_001A_01C50629.953FCF60--