> 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 ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])