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
copy link · copy postgr.es
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