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


> > You might try a multi-column index on (ms.monitorx, ms.datex).
> 
> Just tried it, it didn't prevent the sort.  But it sounds like the sort
> isn't the problem, correct?

The sort isn't actually doing any sorting, so it's virtually free.  The
sort is taking less than 3ms as the data is already 99% sorted due to
the correlation between datex and monitorx.

For similar reasons, the datex index will not be used, as it has no
advantage to being used.

>                            ->  Index Scan using
> monitorstatusx_datex_monitorx_index on monitorstatusx ms
> (cost=0.00..1159.33 rows=890 width=83) (actual time=0.19..1287.02 rows=628
> loops=1)
>                                  Index Cond: (("outer".jdoidx = ms.monitorx)
> AND (ms.datex >= '2003-06-20 08:57:21.36'::timestamp without time zone) AND
> (ms.datex <= '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.

It doesn't appear to have made much difference (looks like data was
partially cached for this new run), but it changed a bit for the better.

I'm afraid thats the best I can do on the query itself I think.


Oh, and using tables in your where clause that aren't in the from clause
is non-portable and often hides bugs:

 from monitorstatusx ms
    , monitorstatusitemx msi
where monitorx.idx = 'M-TEST_1444-TEST_00_10560561260561463219352'

Are you sure you sure you don't have any duplicated constraints by
pulling information in from other tables that you don't need to? 
Removing some of those nested loops would make a significant impact to
the results.

-- 
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc

Attachments:

  signature.asc (application/pgp-signature, 186B) download

view thread (20+ messages)  latest in thread

Message-ID: <1056548165.25587.23.camel@jester>
Permalink:  ../1056548165.25587.23.camel@jester/
Also on:    postgresql.org/message-id/1056548165.25587.23.camel@jester

 · 

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: rbt@rbt.ca, michael.mattox@verideon.com
  Subject: Re: How to optimize monstrous query, sorts instead of
  In-Reply-To: <1056548165.25587.23.camel@jester>
  References: <CJEBLDCHAADCLAGIGCOOGEKECKAA.michael.mattox@verideon.com> <1056548165.25587.23.camel@jester>

* 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