Hi PostgreSQL community,
I have the following query, run immediately after executing VACUUM in
the database. There is only one connection to the database.
The query runs for much longer than I expect it to run for, and I think
this is due to it using the incorrect subplan. As you can see, subplans
1 and 3 make use of and index, but these subplans are not used.
Subplans and 4 are seqscan, and they are used.
How can I get PostgreSQL to use subplan 1 and 3?
Thanks,
Jesse
testdb=> explain analyse SELECT * FROM ARCHIVE_DOCUMENT AS r0 WHERE r0.NODE_ID =
29 AND r0.ARCHIVE_DATE >= '2013-07-08 18:28:00' AND (EXISTS (SELECT r1.* FROM
ARCHIVE_DOCUMENT_INDEX AS r1 WHERE r1.ARCHIVE_ID = r0.ID AND r1.NODE_ID = r0.NODE_ID
AND r1.VALUE = 'BSH70002152' ) OR EXISTS ( SELECT r2.* FROM ARCHIVE_DOCUMENT_INDEX AS
r2 WHERE r2.ARCHIVE_ID = r0.ID AND r2.NODE_ID = r0.NODE_ID AND r2.VALUE = 'TC212592'
) ) ORDER BY r0.ARCHIVE_DATE DESC LIMIT 10;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.56..151.79 rows=10 width=122) (actual time=44601.350..97649.196
rows=2 loops=1)
-> Index Scan Backward using idx_archive_document_x_node_id_archive_date on
archive_document r0 (cost=0.56..8258406.24 rows=546105 width=122) (actual
time=44601.33
Index Cond: ((node_id = 29) AND (archive_date >= '2013-07-08
18:28:00'::timestamp without time zone))
Filter: ((alternatives: SubPlan 1 or hashed SubPlan 2) OR
(alternatives: SubPlan 3 or hashed SubPlan 4))
Rows Removed by Filter: 710851
SubPlan 1
-> Index Only Scan using
archive_document_index_x_archive_id_node_id_value on archive_document_index r1
(cost=0.57..4.59 rows=1 width=0) (never executed)
Index Cond: ((archive_id = r0.id) AND (node_id = r0.node_id)
AND (value = 'BSH70002152'::text))
Heap Fetches: 0
SubPlan 2
-> Seq Scan on archive_document_index r1_1 (cost=0.00..1958104.00
rows=1520 width=16) (actual time=44418.383..44558.293 rows=4 loops=1)
Filter: ((value)::text = 'BSH70002152'::text)
Rows Removed by Filter: 95009919
SubPlan 3
-> Index Only Scan using
archive_document_index_x_archive_id_node_id_value on archive_document_index r2
(cost=0.57..4.59 rows=1 width=0) (never executed)
Index Cond: ((archive_id = r0.id) AND (node_id = r0.node_id)
AND (value = 'TC212592'::text))
Heap Fetches: 0
SubPlan 4
-> Seq Scan on archive_document_index r2_1 (cost=0.00..1958104.00
rows=1520 width=16) (actual time=41659.464..41663.342 rows=1 loops=1)
Filter: ((value)::text = 'TC212592'::text)
Rows Removed by Filter: 95009922
Total runtime: 97683.836 ms
(22 rows)
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
