CODEWITHSUNDEEP
sqlpostgresqlodoo
treffner
treffner

Reputation: 121

Can I optimize this query with an index?

given this query:

SELECT count(u.*)
FROM res_users u
WHERE active=true AND
share=false AND
NOT exists(SELECT 1 FROM res_users_log WHERE create_uid=u.id);

It currently takes 10 seconds.

I tried to make it faster with these 2 index commands, but it didn't help.

CREATE INDEX CONCURRENTLY id_active_share_index ON res_users (id,active,share);
CREATE INDEX CONCURRENTLY create_uid_index ON res_users_log (create_uid);

I guess it's because of the "NOT exists" line, but I have no idea how to include it into an index.

EXPLAIN (ANALYZE, BUFFERS) gives me this output:

                                                                   QUERY PLAN                                                                   
------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=2815437.14..2815437.15 rows=1 width=8) (actual time=39174.365..39174.367 rows=1 loops=1)
   Buffers: shared hit=124 read=112875 dirtied=70, temp read=98788 written=99211
   ->  Merge Anti Join  (cost=2678572.70..2815437.09 rows=20 width=1064) (actual time=39174.360..39174.361 rows=0 loops=1)
         Merge Cond: (u.id = res_users_log.create_uid)
         Buffers: shared hit=124 read=112875 dirtied=70, temp read=98788 written=99211
         ->  Sort  (cost=11.92..11.97 rows=20 width=1068) (actual time=5.577..5.602 rows=16 loops=1)
               Sort Key: u.id
               Sort Method: quicksort  Memory: 79kB
               Buffers: shared hit=53 read=5
               ->  Seq Scan on res_users u  (cost=0.00..11.49 rows=20 width=1068) (actual time=0.050..5.519 rows=16 loops=1)
                     Filter: (active AND (NOT share))
                     Rows Removed by Filter: 33
                     Buffers: shared hit=49 read=5
         ->  Sort  (cost=2678560.78..2716236.90 rows=15070449 width=4) (actual time=36258.796..38013.471 rows=15069209 loops=1)
               Sort Key: res_users_log.create_uid
               Sort Method: external merge  Disk: 206464kB
               Buffers: shared hit=71 read=112870 dirtied=70, temp read=98788 written=99211
               ->  Seq Scan on res_users_log  (cost=0.00..263645.49 rows=15070449 width=4) (actual time=1.755..29961.086 rows=15069319 loops=1)
                     Buffers: shared hit=71 read=112870 dirtied=70
 Planning Time: 0.889 ms
 Execution Time: 39202.694 ms
(21 rows)

Upvotes: 0

Views: 47

Answers (2)

Laurenz Albe
Laurenz Albe

Reputation: 247445

This index will make the query fast as lightning:

CREATE INDEX ON res_users_log (create_uid);

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270573

For this query:

SELECT count(*)
FROM res_users u
WHERE active = true AND
      share = false AND
      NOT exists (SELECT 1 FROM res_users_log rul WHERE rul.create_uid = u.id);

You want indexes on:

  • res_users(active, share, id)
  • res_users_log(create_uid)

Note that the ordering of the columns matters.

Upvotes: 1

Related Questions