Re: How to optimize monstrous query, sorts instead of

> Are jdoidx and monitorx integers?

Yes both are integers:

-- Table: public.monitorstatusx
CREATE TABLE public.monitorstatusx (
averageconnecttimex numeric(65535, 65532),
averagedurationx numeric(65535, 65532),
datex timestamp,
idx varchar(255),
jdoclassx varchar(255),
jdoidx int8 NOT NULL,
jdolockx int4,
monitorx int8,
statusstringx varchar(255),
statusx varchar(255),
CONSTRAINT monitorstatusx_pkey PRIMARY KEY (jdoidx)
) WITH OIDS;

> 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?

-- Index: public.monitorstatusx_datex_monitorx_index
CREATE INDEX monitorstatusx_datex_monitorx_index ON monitorstatusx USING
btree (monitorx, datex);

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=6014.53..6015.86 rows=529 width=788) (actual
time=4286.35..4286.88 rows=626 loops=1)
Sort Key: ms.datex
-> Nested Loop (cost=0.00..5990.59 rows=529 width=788) (actual
time=131.57..4283.76 rows=626 loops=1)
-> Nested Loop (cost=0.00..4388.44 rows=529 width=123) (actual
time=106.23..3398.54 rows=626 loops=1)
-> Nested Loop (cost=0.00..2786.29 rows=529 width=107)
(actual time=90.29..2518.20 rows=626 loops=1)
-> Nested Loop (cost=0.00..1175.81 rows=532 width=91)
(actual time=55.15..1345.88 rows=628 loops=1)
-> Index Scan using monitorx_id_index on
monitorx (cost=0.00..5.36 rows=1 width=8) (actual time=54.94..55.03 rows=1
loops=1)
Index Cond: (idx =
'M-TEST_1444-TEST_00_10560561260561463219352'::character varying)
-> 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))
-> Index Scan using monitorstatus_stjdoidb742c9b3i on
monitorstatus_statusitemsx (cost=0.00..3.01 rows=1 width=16) (actual
time=1.85..1.86 rows=1 loops=628)
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.39..1.39 rows=1 loops=626)
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=1.40..1.40 rows=1
loops=626)
Index Cond: ("outer".statusitemlistx = msi.jdoidx)
Total runtime: 4288.71 msec
(17 rows)

veriguard=#

> 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.

monitorx is a foreign key to the monitorx table.

If the query can't be optimized it's OK, I can live it the speed. I just
couldn't figure out why it'd sort on datex if I had an index on datex.

Thanks,
Michael