Re: How to optimize monstrous query, sorts instead of using index - Mailing list pgsql-performance
From | Michael Mattox |
---|---|
Subject | Re: How to optimize monstrous query, sorts instead of using index |
Date | |
Msg-id | CJEBLDCHAADCLAGIGCOOIEKDCKAA.michael.mattox@verideon.com Whole thread Raw |
In response to | Re: How to optimize monstrous query, sorts instead of using index (Rod Taylor <rbt@rbt.ca>) |
Responses |
Re: How to optimize monstrous query, sorts instead of
|
List | pgsql-performance |
Sorry, I neglected to say the version, yes I'm using Postgres 7.3.2 on Linux. 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. veriguard=# explain analyze 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; QUERY PLAN ---------------------------------------------------------------------------- ---------------------------------------------------------------------------- ---------------------------- Sort (cost=9498.96..9500.27 rows=525 width=788) (actual time=6720.91..6721.44 rows=623 loops=1) Sort Key: ms.datex -> Nested Loop (cost=0.00..9475.26 rows=525 width=788) (actual time=145.16..6718.65 rows=623 loops=1) -> Nested Loop (cost=0.00..7887.69 rows=525 width=123) (actual time=126.84..4528.85 rows=623 loops=1) -> Nested Loop (cost=0.00..6300.13 rows=525 width=107) (actual time=95.37..3470.55 rows=623 loops=1) -> Nested Loop (cost=0.00..4712.13 rows=525 width=91) (actual time=40.44..1892.06 rows=625 loops=1) -> Index Scan using monitorx_id_index on monitorx (cost=0.00..5.48 rows=1 width=8) (actual time=0.25..19.90 rows=1 loops=1) 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) (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)) -> Index Scan using monitorstatus_stjdoidb742c9b3i on monitorstatus_statusitemsx (cost=0.00..3.01 rows=1 width=16) (actual time=2.51..2.51 rows=1 loops=625) Index Cond: ("outer".jdoidx = monitorstatus_statusitemsx.jdoidx) -> Index Scan using monitorstatusitejdoid7db0befci on monitorstatusitemlistd8ea58a5x (cost=0.00..3.01 rows=1 width=16) (actual time=1.68..1.69 rows=1 loops=623) Index Cond: ("outer".statusitemsx = monitorstatusitemlistd8ea58a5x.jdoidx) -> Index Scan using monitorstatusitemx_pkey on monitorstatusitemx msi (cost=0.00..3.01 rows=1 width=665) (actual time=3.50..3.50 rows=1 loops=623) Index Cond: ("outer".statusitemlistx = msi.jdoidx) Total runtime: 6722.43 msec (18 rows)
pgsql-performance by date: