pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Richard Huxton <dev@archonet.com>
To: Ken Egervari <ken@upfactor.com>
Cc: John A Meinel <john@arbash-meinel.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Help with tuning this query (more musings)
Date: Thu, 03 Mar 2005 07:06:47 +0000
Message-ID: <4226B787.3090605@archonet.com> (raw)
In-Reply-To: <001b01c51fbb$39377b90$6a01a8c0@KEN>
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>
	<001a01c51f8f$32bba5b0$6a01a8c0@KEN>
	<000401c51f9b$219ebbd0$6a01a8c0@KEN>
	<001001c51f9b$f606cd40$6a01a8c0@KEN>
	<42269AFB.9010700@arbash-meinel.com>
	<001b01c51fbb$39377b90$6a01a8c0@KEN>

Ken Egervari wrote:
> 
> Hash IN Join  (cost=676.15..1943.11 rows=14 width=91) (actual 
> time=250.000..328.000 rows=39 loops=1)
>  Hash Cond: ("outer".carrier_code_id = "inner".id)
>  ->  Merge Join  (cost=661.65..1926.51 rows=392 width=91) (actual 
> time=250.000..328.000 rows=310 loops=1)
>        Merge Cond: ("outer".current_status_id = "inner".id)
>        ->  Index Scan using shipment_current_status_id_idx on shipment s 
> (cost=0.00..2702.56 rows=27257 width=66) (actual time=0.000..110.000 
> rows=27711 loops=1)
>              Filter: ((current_status_id IS NOT NULL) AND (is_purged = 
> false))

There's a feature in PG called partial indexes - see CREATE INDEX 
reference for details. Basically you can do something like:

CREATE INDEX foo_idx ON shipment (carrier_code_id)
WHERE current_status_id IS NOT NULL
AND is_purged = FALSE;

Something similar may be a win here, although the above index might not 
be quite right - sorry, bit tired at moment.

--
   Richard Huxton
   Archonet Ltd



view thread (4+ messages)

Message-ID: <4226B787.3090605@archonet.com>
Permalink:  ../4226B787.3090605@archonet.com/
Also on:    postgresql.org/message-id/4226B787.3090605@archonet.com

 · 

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: dev@archonet.com, ken@upfactor.com, john@arbash-meinel.com
  Subject: Re: Help with tuning this query (more musings)
  In-Reply-To: <4226B787.3090605@archonet.com>

* 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