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