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: Sun, 30 Jan 2005 00:28:59 +0100
Message-ID: <opsld0ul0nth1vuj@musicbox> (raw)
In-Reply-To: <002201c50654$1a8c50b0$cd422418@a96dfxb4kjzogw>
References: <001601c50416$57090e10$cd422418@a96dfxb4kjzogw>
	<opsldw48czth1vuj@musicbox>
	<002201c50654$1a8c50b0$cd422418@a96dfxb4kjzogw>


> So are you suggesting as a general rule then that sub-queries are the  
> way to force a specific join order in postgres?  If that is the case, I  
> will do this from now on.

	I'll try to explain a bit better...
	Here's your original query :

> 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;

	If you write something like :

SELECT * FROM shipment_status WHERE release_code_id = constant ORDER BY  
release_code_id DESC, date DESC LIMIT 100;

	In this case, if you have an index on (release_code_id, date), the  
planner will use a limited index scan which will yield the rows in index  
order, which will be very fast.

	However, if you just have an index on date, this won't help you.
	In your case, moreover, you don't use release_code_id = constant, but it  
comes from a join. So there may be several different values for  
release_code_id ; thus the planner can't use the optimization, it has to  
find the rows with the release_code_id first. And it can't use the index  
on (release_code_id, date) to get the rows in sorted order precisely  
because there could be several different values for the release_code_id.  
And then it has to sort by date.

	I hope this makes it clearer. If you are absolutely sure there is only  
one row in release_code with r.filtered_column = '5', then this means  
release_code_id is a constant and your query could get a huge speedup by  
writing it differently.



view thread (10+ messages)  latest in thread

Message-ID: <opsld0ul0nth1vuj@musicbox>
Permalink:  ../opsld0ul0nth1vuj@musicbox/
Also on:    postgresql.org/message-id/opsld0ul0nth1vuj@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: <opsld0ul0nth1vuj@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