Indexing (x 4 OR y 5)

Question

I am searching for an index to make the following quick:

CREATE TABLE mytable (
    t1 INT NOT NULL, 
    t2 INT NOT NULL, 
    latest_creation_date INT NOT NULL, 
    data_category_id CHAR(36) NOT NULL, 
    value VARCHAR(255) NOT NULL,  
    PRIMARY KEY(latest_creation_date, t1, t2, data_category_id))

The query is a delete and reads:

delete 
from mytable 
where 
    latest_creation_date = 0 
    and Not (
           (t1 = 5 and t2 = 10)
        or (t1 = 15 and t2 = 20)
        or (t1 = 215 and t2 = 320)
        or (t1 = 315 and t2 = 420)
        ...
        or (t1 = 415 and t2 = 520)
        or (t1 = 515 and t2 = 620));

so it deletes all rows that do not match a few hundred criteria on t1 and t2.

explain gives a sequential scan and rearranges the query:

Delete on mytable  (cost=0.00..2517652.30 rows=38182950 width=6)
   ->  Seq Scan on mytable  (cost=0.00..2517652.30 rows=38182950 width=6)
         Filter: ((latest_creation_date = 0) AND ((t1 <> 5) OR (t2 <> 10)) AND ((t1 <> 15) OR (t2 <> 20)) AND ((t1 <> 215) OR (t2 <> 320)) AND ((t1 <> 315) OR (t2 <> 420)) AND ((t1 <> 415) OR (t2 <> 520)) AND ((t1 <> 515) OR (t2 <> 620)))

Can I create an index that will avoid that full scan?

Thanks in advance!

user_1144035 · Answer

Although it should be the same plan, do either of these result in an improved plan?

delete from mytable 
where latest_creation_date = 0 and
      (t1, t2) not in ((5, 10), (15, 20), . . . );

Or:

delete from mytable 
where latest_creation_date = 0 and
      (t1, t2) not in (select v.*
                       from (values (5, 10), (15, 20), . . .) v(t1, t2)
                      );

user_5330805 · Answer

I applied @GordonLinoff's advice, and came up with his second version.

This has reduced my practical time from 55 Minutes to 14 seconds (table has 37 million rows). Is this possible???

Here is the corresponding explain output:


---------------------------------------------------------------------------------------------------------------------------
 Delete on mytable e  (cost=107.45..1562955.75 rows=19096080 width=6)
   ->  Seq Scan on data_category_evaluation e  (cost=107.45..1562955.75 rows=19096080 width=6)
         Filter: (((stress_test_run_id)::text = 'null'::text) AND (latest_creation_date = 0) AND (NOT (hashed SubPlan 1)))
         SubPlan 1
           ->  Values Scan on "*VALUES*"  (cost=0.00..99.78 rows=3070 width=8)

Indexing (x <> 4 OR y <> 5)

2 Answers