Re: DELETE query and indexes again

Apologies: stats is temptable in this query.

On Thu, Jul 23, 2020 at 2:47 PM Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:

>
>
> On Thu, Jul 23, 2020 at 5:11 PM Wells Oliver <wells(dot)oliver(at)gmail(dot)com>
> wrote:
>
>> This play_statistics table has 96,577,179 rows. Its PK is gid integer,
>> guid uuid, cat text, name text, target integer.
>>
>> Doing a EXPLAIN ANALYZE DELETE WHERE gid IN (SELECT DISTINCT gid FROM
>> temptable) where there is one row in the temptable, I get this:
>>
>> Delete on play_statistics (cost=1984.14..8037586.36 rows=3734075
>> width=34) (actual time=407093.859..407093.859 rows=0 loops=1)
>> -> Hash Join (cost=1984.14..8037586.36 rows=3734075 width=34) (actual
>> time=407093.857..407093.857 rows=0 loops=1)
>> Hash Cond: (play_statistics.gid = "ANY_subquery".gid)
>> -> Seq Scan on play_statistics (cost=0.00..7781818.16 rows=96600516
>> width=10) (actual time=201.521..396052.101 rows=96556116 loops=1)
>> -> Hash (cost=1981.64..1981.64 rows=200 width=32) (actual
>> time=13.020..13.020 rows=1 loops=1)
>> Buckets: 1024 Batches: 1 Memory Usage: 9kB
>> -> Subquery Scan on "ANY_subquery" (cost=1977.64..1981.64 rows=200
>> width=32) (actual time=13.012..13.013 rows=1 loops=1)
>> -> HashAggregate (cost=1977.64..1979.64 rows=200 width=4) (actual
>> time=13.001..13.002 rows=1 loops=1)
>> Group Key: stats.gid
>> -> Seq Scan on stats (cost=0.00..1924.91 rows=21091 width=4) (actual
>> time=0.028..9.244 rows=21091 loops=1)
>> Planning Time: 0.657 ms
>> JIT:
>> Functions: 15
>> Options: Inlining true, Optimization true, Expressions true, Deforming
>> true
>> Timing: Generation 1.816 ms, Inlining 53.472 ms, Optimization 89.014 ms,
>> Emission 58.759 ms, Total 203.060 ms
>> Execution Time: 407112.908 ms
>> (16 rows)
>>
>> It takes 7 minutes to delete 21063 rows for one given gid value, which
>> seems excessive given I think it should be using the PK index.
>>
>> Anything I'm missing here?
>>
>>
> Your command text is over "temptable", but your plan is over "stats". Is
> "temptable" a view?
>
> Cheers,
>
> Jeff
>

--
Wells Oliver
wells(dot)oliver(at)gmail(dot)com <wellsoliver(at)gmail(dot)com>