Skip site navigation (1) Skip section navigation (2)

Slow join query

From: "Tom Tamulewicz" <tomjt7(at)hotmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Slow join query
Date: 2007-06-22 18:32:15
Message-ID: BAY140-F262649D33E1303F364D0E390170@phx.gbl (view raw or flat)
Thread:
Lists: pgsql-performance
<html><div style='background-color:'><P>I have a query that runs about 30-50 seconds.&nbsp; The query is a join between 2 tables (customer and address), each table with about 400,000 rows.&nbsp; My customer table has fields like first_name and last_name where the address table has city, state, etc.&nbsp; I'm using "like"&nbsp;in most of the query columns, which all have indexes.&nbsp; The actual query is:<BR><BR>SELECT p.party_id, p.first_name, p.last_name, pli.address1, pli.city,&nbsp;pli.state FROM customer as p JOIN address as pli ON ( p.party_id = pli.party_id&nbsp;) WHERE&nbsp;( p.void_flag IS NULL OR p.void_flag = false )&nbsp; AND&nbsp; (first_name like 'B%') AND (last_name like 'S%') AND (pli.state like 'M%') AND (pli.city like 'AL%') ORDER BY last_name, first_name LIMIT 51<BR></P>
<P>When the query runs, the hard drive lights up for the duration.&nbsp; (I'm confused by this as 'top' reports only 24k of swap in use).&nbsp; My SUSE 9 test machine has 512 Meg of RAM with 300 Meg&nbsp;used by a Java app.&nbsp; Postmaster reports 56 Meg under "top" and has a 52 Meg segment under&nbsp;"ipcs".&nbsp; I've played with the cache size, shared buffers, and OS shmmax with little change in the query performance.</P>
<P>Q: Would this query benefit from&nbsp;using a view between these two tables?</P>
<P>Q: Any idea why the reported swap usage is so low, yet the query slams the drive?&nbsp; Is postgres not caching this data?&nbsp; If I run the query with the same arguments, it comes right back the second time.&nbsp; If I change the args and re-run, it goes back to the hard drive and takes 30-50 seconds.&nbsp; </P>
<P>Suggestions very welcome,</P>
<P>Tom</P>
<P>&nbsp;</P></div><br clear=all><hr> <a href="http://g.msn.com/8HMAENUS/2728??PS=47575" target="_top">Who's that on the Red Carpet? Play & win glamorous prizes.</a> </html>



Attachment: unknown_filename
Description: text/html (1.8 KB)

Responses

pgsql-performance by date

Next:From: davidDate: 2007-06-22 18:47:20
Subject: Re: PostgreSQL Configuration Tool for Dummies - feedback adjustable control
Previous:From: Tom LaneDate: 2007-06-22 18:02:44
Subject: Re: PITR Backups

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group