pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: PFC <lists@boutiquenumerique.com>
To: Ken Egervari <ken@upfactor.com>
To: pgsql-performance@postgresql.org
Subject: Re: Performance problem with semi-large tables
Date: Sat, 29 Jan 2005 23:08:58 +0100
Message-ID: <opsldw48czth1vuj@musicbox> (raw)
In-Reply-To: <001601c50416$57090e10$cd422418@a96dfxb4kjzogw>
References: <001601c50416$57090e10$cd422418@a96dfxb4kjzogw>



> select s.*, ss.*
> from shipment s, shipment_status ss, release_code r
> where s.current_status_id = ss.id
>    and ss.release_code_id = r.id
>    and r.filtered_column = '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.

	Well, postgres does what you asked. It will be slow, because you have a  
full table join. LIMIT does not change this because the rows have to be  
sorted first.

	The date is in shipment_status so you should first get the  
shipment_status.id that you need and later join to shipment. This will  
avoid the big join :


SELECT s.*, ss.* FROM
	(SELECT * FROM shipment_status WHERE release_code_id IN
		(SELECT r.id FROM release_code WHERE r.filtered_column = '5')
	ORDER BY date DESC LIMIT 100
	) as ss, shipment s
WHERE s.current_status_id = ss.id
ORDER BY date DESC LIMIT 100

	Is this better ?












view thread (10+ messages)  latest in thread

Message-ID: <opsldw48czth1vuj@musicbox>
Permalink:  ../opsldw48czth1vuj@musicbox/
Also on:    postgresql.org/message-id/opsldw48czth1vuj@musicbox

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: lists@boutiquenumerique.com, ken@upfactor.com
  Subject: Re: Performance problem with semi-large tables
  In-Reply-To: <opsldw48czth1vuj@musicbox>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox