gistfile1.sql

SAVEPOINT
Time: 0.144 ms
CREATE INDEX
Time: 1440.867 ms
CREATE INDEX
Time: 1030.121 ms
CREATE INDEX
Time: 1377.586 ms
CREATE INDEX
Time: 1105.998 ms
ANALYZE
Time: 413.712 ms
CREATE TABLE
Time: 2.276 ms
SAVEPOINT
Time: 0.081 ms
consdb=# explain  SELECT Count(DISTINCT place)
FROM act a1
WHERE act_usr_id    = 18369249
AND bin           = 'closed marked'
AND NOT EXISTS (
SELECT visit_id
FROM act a2
WHERE a2.place  = a1.place
AND a2.act_usr_id = a1.act_usr_id
AND a2.arrived    > a1.arrived
AND a2.closing limit 1
);
                                               QUERY PLAN
---------------------------------------------------------------------------------------------------------
 Aggregate  (cost=16012150595.65..16012150595.66 rows=1 width=4)
   ->  Seq Scan on act a1  (cost=0.00..16012150595.30 rows=139 width=4)
         Filter: ((act_usr_id = 18369249) AND ((bin)::text = 'closed marked'::text) AND (NOT (subplan)))
         SubPlan
           ->  Limit  (cost=0.00..27742.47 rows=1 width=4)
                 ->  Seq Scan on act a2  (cost=0.00..27742.47 rows=1 width=4)
                       Filter: (closing AND (arrived > $2) AND (place = $0) AND (act_usr_id = $1))
(7 rows)

Time: 40.544 ms

consdb=# CREATE temp TABLE jefftest (
  act_usr_id int4,
  place int4,
  region_id int4,
  ticket_number varchar(30),
  visit_id int4,
  ticket_closed_date timestamp,
  minutes_worked float4,
  feet_located int4,
  closing bool,
  category varchar(30),
  description varchar(60),
  relevant boolean,
  arrived timestamp,
  bin varchar(30)
) WITHOUT OIDS on commit drop;
CREATE TABLE
Time: 1.534 ms
consdb=# COPY jefftest FROM '/tmp/act';
COPY 567582
Time: 68927.215 ms
consdb=# create index jefftest_jefftest_usr_id on jefftest(act_usr_id);
CREATE INDEX
Time: 1001.234 ms
consdb=# create index jefftest_place on jefftest(place);
CREATE INDEX
Time: 868.010 ms
consdb=# create index jefftest_arrived on jefftest(arrived);
CREATE INDEX
Time: 1066.034 ms
consdb=# create index jefftest_closing on jefftest(closing);
CREATE INDEX
Time: 925.386 ms
consdb=# analyze jefftest;
ANALYZE
Time: 315.086 ms
consdb=# explain  SELECT Count(DISTINCT place)
FROM jefftest a1
WHERE act_usr_id    = 18369249
AND bin           = 'closed marked'
AND NOT EXISTS (
SELECT visit_id
FROM jefftest a2
WHERE a2.place  = a1.place
AND a2.act_usr_id = a1.act_usr_id
AND a2.arrived    > a1.arrived
AND a2.closing limit 1
);
                                                QUERY PLAN                                                 
-----------------------------------------------------------------------------------------------------------
 Aggregate  (cost=10358.17..10358.18 rows=1 width=4)
   ->  Bitmap Heap Scan on jefftest a1  (cost=17.38..10357.83 rows=135 width=4)
         Recheck Cond: (act_usr_id = 18369249)
         Filter: (((bin)::text = 'closed marked'::text) AND (NOT (subplan)))
         ->  Bitmap Index Scan on jefftest_jefftest_usr_id  (cost=0.00..17.34 rows=668 width=0)
               Index Cond: (act_usr_id = 18369249)
         SubPlan
           ->  Limit  (cost=0.00..12.37 rows=1 width=4)
                 ->  Index Scan using jefftest_place on jefftest a2  (cost=0.00..12.37 rows=1 width=4)
                       Index Cond: (place = $0)
                       Filter: (closing AND (arrived > $2) AND (act_usr_id = $1))
(11 rows)

Time: 1.096 ms

consdb=# create temp table jefftest2 AS select * from act;
SELECT
Time: 1608.868 ms
consdb=# create index jefftest2_jefftest_usr_id on jefftest2(act_usr_id);
CREATE INDEX
Time: 996.708 ms
consdb=# create index jefftest2_place on jefftest2(place);
CREATE INDEX
Time: 848.467 ms
consdb=# create index jefftest2_arrived on jefftest2(arrived);
CREATE INDEX
Time: 1041.094 ms
consdb=# create index jefftest2_closing on jefftest2(closing);
CREATE INDEX
Time: 886.795 ms
consdb=# analyze jefftest2;
ANALYZE
Time: 287.655 ms
consdb=# explain  SELECT Count(DISTINCT place)
FROM jefftest2 a1
WHERE act_usr_id    = 18369249
AND bin           = 'closed marked'
AND NOT EXISTS (
SELECT visit_id
FROM jefftest2 a2
WHERE a2.place  = a1.place
AND a2.act_usr_id = a1.act_usr_id
AND a2.arrived    > a1.arrived
AND a2.closing limit 1
);
                                                 QUERY PLAN                                                  
-------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=10286.27..10286.28 rows=1 width=4)
   ->  Bitmap Heap Scan on jefftest2 a1  (cost=13.33..10285.92 rows=138 width=4)
         Recheck Cond: (act_usr_id = 18369249)
         Filter: (((bin)::text = 'closed marked'::text) AND (NOT (subplan)))
         ->  Bitmap Index Scan on jefftest2_jefftest_usr_id  (cost=0.00..13.30 rows=665 width=0)
               Index Cond: (act_usr_id = 18369249)
         SubPlan
           ->  Limit  (cost=0.00..12.34 rows=1 width=4)
                 ->  Index Scan using jefftest2_place on jefftest2 a2  (cost=0.00..12.34 rows=1 width=4)
                       Index Cond: (place = $0)
                       Filter: (closing AND (arrived > $2) AND (act_usr_id = $1))
(11 rows)

Time: 1.021 ms

consdb=# explain  SELECT Count(DISTINCT place)
FROM act a1
WHERE act_usr_id    = 18369249
AND bin           = 'closed marked'
AND NOT EXISTS (
SELECT visit_id
FROM act a2
WHERE a2.place  = a1.place
AND a2.act_usr_id = a1.act_usr_id
AND a2.arrived    > a1.arrived
AND a2.closing limit 1
);
                                               QUERY PLAN                                                
---------------------------------------------------------------------------------------------------------
 Aggregate  (cost=16012150595.65..16012150595.66 rows=1 width=4)
   ->  Seq Scan on act a1  (cost=0.00..16012150595.30 rows=139 width=4)
         Filter: ((act_usr_id = 18369249) AND ((bin)::text = 'closed marked'::text) AND (NOT (subplan)))
         SubPlan
           ->  Limit  (cost=0.00..27742.47 rows=1 width=4)
                 ->  Seq Scan on act a2  (cost=0.00..27742.47 rows=1 width=4)
                       Filter: (closing AND (arrived > $2) AND (place = $0) AND (act_usr_id = $1))
(7 rows)

Time: 0.651 ms



consdb=# explain analyze  SELECT Count(DISTINCT place)
FROM act a1
WHERE act_usr_id    = 18369249
AND bin           = 'closed marked'
AND NOT EXISTS (
SELECT visit_id
FROM act a2
WHERE a2.place  = a1.place
AND a2.act_usr_id = a1.act_usr_id
AND a2.arrived    > a1.arrived
AND a2.closing limit 1
);
                                                          QUERY PLAN                                                          
------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=16012150595.65..16012150595.66 rows=1 width=4) (actual time=49180.749..49180.751 rows=1 loops=1)
   ->  Seq Scan on act a1  (cost=0.00..16012150595.30 rows=139 width=4) (actual time=618.741..49180.214 rows=101 loops=1)
         Filter: ((act_usr_id = 18369249) AND ((bin)::text = 'closed marked'::text) AND (NOT (subplan)))
         SubPlan
           ->  Limit  (cost=0.00..27742.47 rows=1 width=4) (actual time=474.182..474.182 rows=0 loops=103)
                 ->  Seq Scan on act a2  (cost=0.00..27742.47 rows=1 width=4) (actual time=474.175..474.175 rows=0 loops=103)
                       Filter: (closing AND (arrived > $2) AND (place = $0) AND (act_usr_id = $1))
 Total runtime: 49180.818 ms
(8 rows)

Time: 49181.536 ms


consdb=# explain ANALYZE  SELECT Count(DISTINCT place)
FROM jefftest2 a1
WHERE act_usr_id    = 18369249
AND bin           = 'closed marked'
AND NOT EXISTS (
SELECT visit_id
FROM jefftest2 a2
WHERE a2.place  = a1.place
AND a2.act_usr_id = a1.act_usr_id
AND a2.arrived    > a1.arrived
AND a2.closing limit 1
);
                                                                       QUERY PLAN                                                                        
---------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=10286.27..10286.28 rows=1 width=4) (actual time=4.129..4.131 rows=1 loops=1)
   ->  Bitmap Heap Scan on jefftest2 a1  (cost=13.33..10285.92 rows=138 width=4) (actual time=0.228..3.890 rows=101 loops=1)
         Recheck Cond: (act_usr_id = 18369249)
         Filter: (((bin)::text = 'closed marked'::text) AND (NOT (subplan)))
         ->  Bitmap Index Scan on jefftest2_jefftest_usr_id  (cost=0.00..13.30 rows=665 width=0) (actual time=0.127..0.127 rows=125 loops=1)
               Index Cond: (act_usr_id = 18369249)
         SubPlan
           ->  Limit  (cost=0.00..12.34 rows=1 width=4) (actual time=0.019..0.019 rows=0 loops=103)
                 ->  Index Scan using jefftest2_place on jefftest2 a2  (cost=0.00..12.34 rows=1 width=4) (actual time=0.015..0.015 rows=0 loops=103)
                       Index Cond: (place = $0)
                       Filter: (closing AND (arrived > $2) AND (act_usr_id = $1))
 Total runtime: 4.213 ms
(12 rows)