Re: Incorrect index being used

On 09/10/2013 12:57, Jesse Long wrote:
> On 09/10/2013 12:10, Albe Laurenz wrote:
>> 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
>>
>
> Hi Laurenz,
>
> Thank you for the feedback.
>
> There is no problem with row visibility, there is only one connection
> to the database - the connection I am using to do these selects.
>
> Thanks you for the advise regarding ANALYZE. I ran ANALYZE on both
> tables concerned, but not much changed:
>
> QUERY PLAN
>
> ------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>
> Limit (cost=0.56..151.85 rows=10 width=122) (actual
> time=40841.984..85668.213 rows=2 loops=1)
>
> -> Index Scan Backward using
> idx_archive_document_x_node_id_archive_date on archive_document r0
> (cost=0.56..7627640.20 rows=504186 width=122) (actual time=40841.98
>
> 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..1958101.80 rows=1568 width=16) (actual
> time=36633.365..40841.909 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..1958101.80 rows=1568 width=16) (actual
> time=40241.599..44462.485 rows=1 loops=1)
>
> Filter: ((value)::text = 'TC212592'::text)
>
> Rows Removed by Filter: 95009922
>
> Total runtime: 85676.734 ms
>
> (22 rows)

Sorry, I neglected to mention that this is on PostgreSQL 9.3.0.

Thanks,
Jesse