pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Ken Egervari <ken@upfactor.com>
To: pgsql-performance@postgresql.org
Subject: Re: Help with tuning this query (Some musings)
Date: Wed, 2 Mar 2005 21:52:39 -0500
Message-ID: <002a01c51f9c$1066d0e0$6a01a8c0@KEN> (raw)
References: <001001c51ef4$3849eeb0$6a01a8c0@KEN>
	<1109787227.31084.12.camel@localhost.localdomain>
	<000901c51f55$aa4b7400$6a01a8c0@KEN>
	<1109789394.31084.20.camel@localhost.localdomain>
	<000601c51f63$63c8bb10$6a01a8c0@KEN>
	<13490.1109795370@sss.pgh.pa.us>
	<000801c51f67$c3abdcc0$6a01a8c0@KEN>
	<14444.1109802588@sss.pgh.pa.us>

I took John's advice and tried to work with sub-selects.  I tried this
variation, which actually seems like it would make a difference conceptually
since it drives on the person table quickly.  But to my surprise, the query
runs at about 375 milliseconds.  I think it's because it's going over that
shipment table multiple times, which is where the results are coming from.

select s.*, ss.*

from shipment s
 inner join shipment_status ss on s.current_status_id=ss.id
 inner join release_code rc on ss.release_code_id=rc.id
 left outer join driver d on s.driver_id=d.id
 left outer join carrier_code cc on s.carrier_code_id=cc.id
where s.id in (
  select s.id
  from person p
   inner join carrier_to_person ctp on p.id=ctp.person_id
   inner join carrier c on ctp.carrier_id=c.id
   inner join carrier_code cc on cc.carrier_id = c.id
   inner join shipment s on s.carrier_code_id = cc.id
  where p.id = 355
      and s.current_status_id is not null
      and s.is_purged=false
 )
 and(rc.number='9' )
 and(ss.date>=current_date-31 )

order by ss.date desc

*** Musing 1
Also, "s.current_status_id is not null" is an important filter that I forgot
to mention.  In this example where p.id = 355, it filters out 90% of the
rows.  In general, that filter ratio is 0.46 though, which is not quite so
high.  However, this filter gets better over time because more and more
users will use a filter that will make this value null.  It's still not as
strong as person though and probably never will be.  But I thought I'd
mention it nonetheless.

*** Musing 2
I do think that the filter "ss.date>=current_date-31" is slowing this query
down.  I don't think it's the mention of "current_date" or even that it's
dynamic instead of static.  I think the range is just too big.  For example,
if I use:

and ss.date between '2005-02-01 00:00:00' and '2005-02-28 23:59:59'

The query still results in 250 milliseconds.  But if I make the range very
small - say Feb 22nd of 2005:

and ss.date between '2005-02-22 00:00:00' and '2005-02-22 23:59:59'

Now the entire query runs in 47 milliseconds on average.  If I can't make
this query perform any better, should I change the user interface to select
the date instead of showing the last 31 days to benefit from this single-day
filter?  This causes more clicks to select the day (like from a calendar),
but most users probably aren't interested in seeing the entire listing
anyway.  However, it's a very important requirement that users know that
shipment enteries exist in the last 31 days (because they are usually
sure-fire problems if they are still in this query after a few days).

I guess I'm wondering if tuning the query is futile and I should get the
requirements changed, or is there something I can do to really speed it up?

Thanks again,
Ken




Message-ID: <002a01c51f9c$1066d0e0$6a01a8c0@KEN>
Permalink:  ../002a01c51f9c$1066d0e0$6a01a8c0@KEN/
Also on:    postgresql.org/message-id/002a01c51f9c$1066d0e0$6a01a8c0@KEN

 · 

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: ken@upfactor.com
  Subject: Re: Help with tuning this query (Some musings)
  In-Reply-To: <002a01c51f9c$1066d0e0$6a01a8c0@KEN>

* 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