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 using index
Date: 25 Jun 2003 07:54:20 -0400
Message-ID: <1056542059.69651.73.camel@jester> (raw)
In-Reply-To: <CJEBLDCHAADCLAGIGCOOIEKCCKAA.michael.mattox@verideon.com>
References: <CJEBLDCHAADCLAGIGCOOIEKCCKAA.michael.mattox@verideon.com>

Is this 7.3.x?  Can we see explain analyze output for the query?

On Wed, 2003-06-25 at 07:46, Michael Mattox wrote:
> I've used indexes to speed up my queries but this query escapes me.  I'm
> curious if someone can suggest an index or a way to modify the query to use
> the index.  The query is:
> 
> select ms.averageconnecttimex as ms_averageconnecttime, ms.averagedurationx
> as ms_averageduration, ms.datex as ms_date, ms.idx as ms_id,
> ms.statusstringx as ms_statusstring, ms.statusx as ms_status,
> msi.actualcontentx as msi_actualcontent, msi.connecttimex as
> msi_connecttime, msi.correctcontentx as msi_correctcontent, msi.datex as
> msi_date, msi.descriptionx as msi_description, msi.durationx as
> msi_duration, msi.errorcontentx as msi_errorcontent, msi.idx as msi_id,
> msi.monitorlocationx as msi_monitorlocation, msi.statusstringx as
> msi_statusstring, msi.statusx as msi_status from monitorstatusx ms,
> monitorstatusitemx msi where monitorx.idx =
> 'M-TEST_1444-TEST_00_10560561260561463219352' AND monitorx.jdoidx =
> ms.monitorx AND ms.datex >= '2003-06-20 08:57:21.36' AND ms.datex <=
> '2003-06-29 08:57:21.36' AND ms.jdoidx = monitorstatus_statusitemsx.jdoidx
> AND monitorstatus_statusitemsx.statusitemsx =
> monitorstatusitemlistd8ea58a5x.jdoidx AND
> monitorstatusitemlistd8ea58a5x.statusitemlistx = msi.jdoidx ORDER BY ms_date
> DESC;
> 
> Here is the result of explain:
> 
>  Sort  (cost=9498.85..9500.16 rows=525 width=788)
>    Sort Key: ms.datex
>    ->  Nested Loop  (cost=0.00..9475.15 rows=525 width=788)
>          ->  Nested Loop  (cost=0.00..7887.59 rows=525 width=123)
>                ->  Nested Loop  (cost=0.00..6300.03 rows=525 width=107)
>                      ->  Nested Loop  (cost=0.00..4712.02 rows=525 width=91)
>                            ->  Index Scan using monitorx_id_index on
> monitorx  (cost=0.00..5.37 rows=1 width=8)
>                                  Index Cond: (idx =
> 'M-TEST_1444-TEST_00_10560561260561463219352'::character varying)
>                            ->  Index Scan using monitorstatusxmonitori on
> monitorstatusx ms  (cost=0.00..4695.65 rows=880 width=83)
>                                  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))
>                      ->  Index Scan using monitorstatus_stjdoidb742c9b3i on
> monitorstatus_statusitemsx  (cost=0.00..3.01 rows=1 width=16)
>                            Index Cond: ("outer".jdoidx =
> monitorstatus_statusitemsx.jdoidx)
>                ->  Index Scan using monitorstatusitejdoid7db0befci on
> monitorstatusitemlistd8ea58a5x  (cost=0.00..3.01 rows=1 width=16)
>                      Index Cond: ("outer".statusitemsx =
> monitorstatusitemlistd8ea58a5x.jdoidx)
>          ->  Index Scan using monitorstatusitemx_pkey on monitorstatusitemx
> msi  (cost=0.00..3.01 rows=1 width=665)
>                Index Cond: ("outer".statusitemlistx = msi.jdoidx)
> (17 rows)
> 
> As you can see, it's doing a sort on ms.datex.  I created an index on the
> monitorstatusx (ms) table for the datex, but it doesn't use it.  Is it
> possible to create an index to prevent this sort?
> 
> Thanks,
> Michael
> 
> 
> Michael Mattox
> cunparis@yahoo.fr / http://www.advweb.com/michael
> 
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly
-- 
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: <1056542059.69651.73.camel@jester>
Permalink:  ../1056542059.69651.73.camel@jester/
Also on:    postgresql.org/message-id/1056542059.69651.73.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 using index
  In-Reply-To: <1056542059.69651.73.camel@jester>
  References: <CJEBLDCHAADCLAGIGCOOIEKCCKAA.michael.mattox@verideon.com> <1056542059.69651.73.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