Re: Incorrect index being used

Jesse Long wrote:
> I have the following query, run immediately after executing VACUUM in
> the database. There is only one connection to the database.

You should run ANALYZE, not VACUUM.

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

They are only possible if an "Index Only Scan" is possible, which
can only be used if the respective table entries are visible for
all transactions.

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

[...]

>           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

The estimates are quite off.
Does "ANALYZE archive_document", possibly after increasing
default_statistics_target, make a difference?

Yours,
Laurenz Albe