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 08:12:09 -0400
Message-ID: <1056543129.69651.82.camel@jester> (raw)
In-Reply-To: <CJEBLDCHAADCLAGIGCOOIEKDCKAA.michael.mattox@verideon.com>
References: <CJEBLDCHAADCLAGIGCOOIEKDCKAA.michael.mattox@verideon.com>

> Here's the output of explain analyze.  The query typically takes 0-4 seconds
> depending on the time frame.  It's run very frequently especially to process
> the nightly reports.

The plan picked seems reasonable (estimated costs / tuples is close to
actual).

I think the biggest hit is this index scan.  Thats a substantial cost to
pull out less than a thousand lines:

                           ->  Index Scan using monitorstatusxmonitori
on
monitorstatusx ms  (cost=0.00..4695.65 rows=880 width=83) (actual
time=40.17..1868.12 rows=625 loops=1)
                                 Index Cond: ("outer".jdoidx =
ms.monitorx)
                                 Filter: ((datex >= '2003-06-20
08:57:21.36'::timestamp without time zone) AND (datex <= '2003-06-29
08:57:21.36'::timestamp without time zone))


Are jdoidx and monitorx integers?

You might try a multi-column index on (ms.monitorx, ms.datex).

Are monitorx assigned roughly ordered by date?  It must be, otherwise
the sort step would not be so cheap (hardly any impact on the query --
see actual cost number).  The multi-column index above should give you a
bit of a boost.

Depending on the data in the table, the index (ms.datex, monitorx) may
give better results along with a single index on (ms.monitorx) as you
currently have.  It's not very likely though.

-- 
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: <1056543129.69651.82.camel@jester>
Permalink:  ../1056543129.69651.82.camel@jester/
Also on:    postgresql.org/message-id/1056543129.69651.82.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: <1056543129.69651.82.camel@jester>
  References: <CJEBLDCHAADCLAGIGCOOIEKDCKAA.michael.mattox@verideon.com> <1056543129.69651.82.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