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 DBB903A5933 for ; Thu, 27 Jan 2005 02:17:36 +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 87763-08 for ; Thu, 27 Jan 2005 02:17:18 +0000 (GMT) Received: from fep7.cogeco.net (smtp.cogeco.net [216.221.81.25]) by svr1.postgresql.org (Postfix) with ESMTP id 75A983A5D3F for ; Thu, 27 Jan 2005 02:17: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 03E5CA77 for ; Wed, 26 Jan 2005 21:17:19 -0500 (EST) Received: (qmail 11258 invoked from network); 27 Jan 2005 02:46:51 -0000 Received: from d36-66-205.home1.cgocable.net (HELO a96dfxb4kjzogw) (24.36.66.205) by 0 with SMTP; 27 Jan 2005 02:46:51 -0000 Message-ID: <001601c50416$57090e10$cd422418@a96dfxb4kjzogw> From: "Ken Egervari" To: Subject: Performance problem with semi-large tables Date: Wed, 26 Jan 2005 21:17:23 -0500 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0013_01C503EC.6DE1FFE0" 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-Archive-Number: 200501/609 X-Sequence-Number: 10299 This is a multi-part message in MIME format. ------=_NextPart_000_0013_01C503EC.6DE1FFE0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable 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_0013_01C503EC.6DE1FFE0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hi everyone.
 
I'm new to this forum and was wondering = if anyone=20 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 = explaination=20 will suffice.
 
I have a 2 tables that are are getting = large and=20 will only get larger with time (expoentially as more users sign on to = the=20 system).  Right the now, a table called 'shipment' contains about = 16,000=20 rows and 'shipment_status' contains about 32,500 rows.  These = aren't=20 massive rows (I keep reading about tables with millions), but they will=20 definately get into 6 digits by next year and query performance is quite = poor.
 
Now, from what I can understand about = tuning, you=20 want to specify good filters, provide good indexes on the driving filter = as well=20 as any referencial keys that are used while joining.  This has = helped me=20 solve performance problems many times in the past (for example, changing = a query=20 speed from 2 seconds to 21 milliseconds). 
 
However, I am now tuning queries that = operate on=20 these two tables and the filters aren't very good (the best is a filter = ratio of=20 0.125) and the number of rows returned is very large (not taking into=20 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, = release_code=20 r
where s.current_status_id =3D = ss.id
   and ss.release_code_id =3D = 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 rows=20 by looking at the production data, hence the 0.125 filter ratio.  = However,=20 the data distribution is not normal since the filtered column actually = pulls out=20 about 54% of the rows in shipment_status when it joins.  Postgres = seems to=20 be doing a sequencial scan to pull out all of these rows.  Next, it = joins=20 approx 17550 rows to shipment.  Since this query has a limit, it = only=20 returns the first 100, which seems like a waste.
 
Now, for this query, I know I can = filter out the=20 date instead to speed it up.  For example, I can probably search = for all=20 the shipments in the last 3 days instead of limiting it to 100.  = But since=20 this isn't a real production query, I only wanted to show it as an = example since=20 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=20 this more efficient since all I see is a bunch of hash joins and = sequencial=20 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 access=20 those?
2. Can you recommend ways so that = postgres acts on=20 big tables more efficiently?  I'm not really interested in this = specific=20 case (I just made it up).  I'm more interested in general solutions = to this=20 general problem of big table sizes with bad filters and where join = orders don't=20 seem to help much.
 
Thank you very much for your=20 help.
 
Best Regards,
Ken Egervari
------=_NextPart_000_0013_01C503EC.6DE1FFE0--