pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Michael Mattox <michael.mattox@verideon.com>
To: Postgresql Performance <pgsql-performance@postgresql.org>
Subject: How to optimize monstrous query, sorts instead of using index
Date: Wed, 25 Jun 2003 13:46:48 +0200
Message-ID: <CJEBLDCHAADCLAGIGCOOIEKCCKAA.michael.mattox@verideon.com> (raw)

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






view thread (20+ messages)  latest in thread

Message-ID: <CJEBLDCHAADCLAGIGCOOIEKCCKAA.michael.mattox@verideon.com>
Permalink:  ../CJEBLDCHAADCLAGIGCOOIEKCCKAA.michael.mattox@verideon.com/
Also on:    postgresql.org/message-id/CJEBLDCHAADCLAGIGCOOIEKCCKAA.michael.mattox@verideon.com

 · 

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: michael.mattox@verideon.com
  Subject: Re: How to optimize monstrous query, sorts instead of using index
  In-Reply-To: <CJEBLDCHAADCLAGIGCOOIEKCCKAA.michael.mattox@verideon.com>
  References: <CJEBLDCHAADCLAGIGCOOIEKCCKAA.michael.mattox@verideon.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