Created
October 30, 2008 21:54
-
-
Save jfrost/21154 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment