mbjSCJVu

1DROP TABLE IF EXISTS history;
2CREATE TABLE history (
3    id integer NOT NULL,
4    ticket_id integer NOT NULL);
5ALTER TABLE ONLY history ADD CONSTRAINT history_pkey PRIMARY KEY (id);
6CREATE INDEX history_ticket_id ON history USING btree (ticket_id);
7DROP TABLE IF EXISTS ticket;
8CREATE TABLE ticket (
9    id integer NOT NULL
10);
11ALTER TABLE ONLY ticket ADD CONSTRAINT ticket_pkey PRIMARY KEY (id);
12	
13INSERT INTO history values (generate_series(1, 30000), generate_series(1, 30000));
14ANALYZE history;
15
16INSERT INTO ticket values (generate_series(1, 40000));
17ANALYZE ticket;
18	
19explain analyze select distinct ticket_id from history
20       where ticket_id not in (select id from ticket);
21	
22HashAggregate  (cost=15510545.50..15510695.50 rows=15000 width=4) (actual time=170892.668..170892.668 rows=0 loops=1)
23   ->  Seq Scan on history  (cost=0.00..15510508.00 rows=15000 width=4) (actual time=170892.644..170892.644 rows=0 loops=1)
24         Filter: (NOT (SubPlan 1))
25         Rows Removed by Filter: 30000
26         SubPlan 1
27           ->  Materialize  (cost=0.00..934.00 rows=40000 width=4) (actual time=0.006..2.685 rows=15000 loops=30000)
28                 ->  Seq Scan on ticket  (cost=0.00..577.00 rows=40000 width=4) (actual time=0.038..21.347 rows=30000 loops=1)
29 Total runtime: 170892.965 ms
30	
31explain analyze select distinct ticket_id from history
32       except select id from ticket;
33	
34HashSetOp Except  (cost=0.29..2449.29 rows=30000 width=4) (actual time=41.641..41.641 rows=0 loops=1)
35   ->  Append  (cost=0.29..2274.29 rows=70000 width=4) (actual time=0.024..27.835 rows=70000 loops=1)
36         ->  Subquery Scan on "*SELECT* 1"  (cost=0.29..1297.29 rows=30000 width=4) (actual time=0.024..14.527 rows=30000 loops=1)
37               ->  Unique  (cost=0.29..997.29 rows=30000 width=4) (actual time=0.022..10.856 rows=30000 loops=1)
38                     ->  Index Only Scan using history_ticket_id on history  (cost=0.29..922.29 rows=30000 width=4) (actual time=0.021..6.031 rows=30000 loops=1)
39                           Heap Fetches: 30000
40         ->  Subquery Scan on "*SELECT* 2"  (cost=0.00..977.00 rows=40000 width=4) (actual time=0.018..8.364 rows=40000 loops=1)
41               ->  Seq Scan on ticket  (cost=0.00..577.00 rows=40000 width=4) (actual time=0.018..3.808 rows=40000 loops=1)
42 Total runtime: 41.702 ms
43	
44explain analyze select distinct ticket_id from history h
45       where not EXISTS (select id from ticket t where t.id = h.ticket_id);
46	
47Unique  (cost=0.58..2294.04 rows=1 width=4) (actual time=23.140..23.140 rows=0 loops=1)
48  ->  Merge Anti Join  (cost=0.58..2294.04 rows=1 width=4) (actual time=23.139..23.139 rows=0 loops=1)
49        Merge Cond: (h.ticket_id = t.id)
50        ->  Index Only Scan using history_ticket_id on history h  (cost=0.29..922.29 rows=30000 width=4) (actual time=0.037..6.848 rows=30000 loops=1)
51              Heap Fetches: 30000
52        ->  Index Only Scan using ticket_pkey on ticket t  (cost=0.29..1228.29 rows=40000 width=4) (actual time=0.026..6.970 rows=30000 loops=1)
53              Heap Fetches: 30000
54Total runtime: 23.189 ms