Re: More correlated (?) index woes

On 28 March 2016 at 22:01, rob stone <floriparob(at)gmail(dot)com> wrote:

> What does:-
>
> DELETE FROM pa
> WHERE pa.field1 IS NULL
> AND pa.sc_id IN (SELECT legs.sc_id FROM legs
> WHERE legs.scdate BETWEEN 20160220 AND > 20160222)
>
> give as a cost when you run ANALYZE over it?
>

​Thanks for the suggestion.

It's a
​pproximately the same.

Delete on pa (cost=1463.31..493321.89 rows=187833 width=12) (actual
time=41539.174..41539.174 rows=0 loops=1)
-> Hash Join (cost=1463.31..493321.89 rows=187833 width=12) (actual
time=41539.172..41539.172 rows=0 loops=1)
Hash Cond: (pa.sc_id = legs.sc_id)
-> Seq Scan on pa (cost=0.00..480888.83 rows=2899078 width=10)
(actual time=0.010..40866.049 rows=2591264 loops=1)
Filter: (field1 IS NULL)
Rows Removed by Filter: 4931412
-> Hash (cost=1321.48..1321.48 rows=11346 width=10) (actual
time=29.481..29.481 rows=20940 loops=1)
Buckets: 32768 (originally 16384) Batches: 1 (originally 1)
Memory Usage: 1156kB
-> HashAggregate (cost=1208.02..1321.48 rows=11346
width=10) (actual time=20.446..25.028 rows=20940 loops=1)
Group Key: legs.sc_id
-> Index Scan using legs_scdate_idx on legs
(cost=0.43..1171.88 rows=14458 width=10) (actual time=0.025..13.133
rows=21281 loops=1)
Index Cond: ((scdate >= 20160220) AND (scdate <=
20160222))

I've tried creating a subquery out of the legs dataset in the hope that
that would help but that made no difference either.

Geoff​