Postgres Pro
Downloads
Home   >   mailing lists

Re: More correlated (?) index woes - Mailing list pgsql-general

From Geoff Winkless
Subject Re: More correlated (?) index woes
Date
Msg-id CAEzk6fcSe3aE2WbpUHc+_3nhozgPR4oFODE9aZAkNtxd6irBCg@mail.gmail.com
Whole thread Raw
In response to Re: More correlated (?) index woes  (rob stone <floriparob@gmail.com>)
List pgsql-general
Tree view
On 28 March 2016 at 22:01, rob stone <floriparob@gmail.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​

pgsql-general by date:

Previous
From: rob stone
Date:
Subject: Re: More correlated (?) index woes
Next
From: Igor Neyman
Date:
Subject: Re: Way to get at parsed trigger 'WHEN' clause expression?