pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Rod Taylor <rbt@rbt.ca>
Cc: michael.mattox@verideon.com, Postgresql Performance <pgsql-performance@postgresql.org>
Subject: Re: How to optimize monstrous query, sorts instead of
Date: Wed, 25 Jun 2003 11:55:09 -0400
Message-ID: <3648.1056556509@sss.pgh.pa.us> (raw)
In-Reply-To: <1056548165.25587.23.camel@jester>
References: <CJEBLDCHAADCLAGIGCOOGEKECKAA.michael.mattox@verideon.com>
	<1056548165.25587.23.camel@jester>

Rod Taylor <rbt@rbt.ca> writes:
>> monitorstatusx_datex_monitorx_index on monitorstatusx ms
>> (cost=3D0.00..1159.33 rows=3D890 width=3D83) (actual time=3D0.19..1287.02=
>  rows=3D628
>> loops=3D1)
>> Index Cond: (("outer".jdoidx =3D ms.moni=
> torx)
>> AND (ms.datex >=3D '2003-06-20 08:57:21.36'::timestamp without time zone)=
>  AND
>> (ms.datex <=3D '2003-06-29 08:57:21.36'::timestamp without time zone))

> You can see that it used the new multi-key index for both items, rather
> than finding for monitorx, then filtering out unwanted results by datex.

What is the column ordering of the combined index?  Unless datex is the
first column, there is no chance of using it to create the required sort
order anyway.  I think this index condition is suggesting that monitorx
is the first column.

However, I agree with Rod's point that "avoid the sort" is not the
mindset to use to optimize this query. The joins are the problem.
You might try forcing different join types (see enable_nestloop and
friends) to get an idea of whether a different plan is likely to help.

			regards, tom lane



view thread (20+ messages)  latest in thread

Message-ID: <3648.1056556509@sss.pgh.pa.us>
Permalink:  ../3648.1056556509@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/3648.1056556509@sss.pgh.pa.us

 · 

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: tgl@sss.pgh.pa.us, rbt@rbt.ca
  Subject: Re: How to optimize monstrous query, sorts instead of
  In-Reply-To: <3648.1056556509@sss.pgh.pa.us>
  References: <CJEBLDCHAADCLAGIGCOOGEKECKAA.michael.mattox@verideon.com> <1056548165.25587.23.camel@jester> <3648.1056556509@sss.pgh.pa.us>

* 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