Created
January 8, 2011 00:47
-
-
Save springmeyer/770381 to your computer and use it in GitHub Desktop.
benefit to having !bbox! query manually placed inside subselect beside other WHERE?
This file contains hidden or 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
/* (SELECT * FROM polygons9 WHERE iucn_category_id = 2) AS GEOM */ | |
tiledb=# explain analyze SELECT AsBinary("the_geom") AS geom from | |
(SELECT * FROM polygons9 WHERE iucn_category_id = 2) AS GEOM | |
WHERE "the_geom" && SetSRID('BOX3D(-20037510 2505758.018505042,60267.60323439911 8276874.507094959)'::box3d, 900913); | |
QUERY PLAN | |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | |
Bitmap Heap Scan on polygons9 (cost=19.85..264.07 rows=8 width=93117) (actual time=1.173..2.883 rows=21 loops=1) | |
Recheck Cond: (the_geom && '010300002031BF0D00010000000500000000000060F81B73C18B5F5E020F1E434100000060F81B73C16A3E74A0E2925F41003AB24D736DED406A3E74A0E2925F41003AB24D736DED408B5F5E020F1E434100000060F81B73C18B5F5E020F1E4341'::geometry) | |
Filter: (iucn_category_id = 2) | |
-> Bitmap Index Scan on polygons9_gist_idx (cost=0.00..19.85 rows=480 width=0) (actual time=0.306..0.306 rows=350 loops=1) | |
Index Cond: (the_geom && '010300002031BF0D00010000000500000000000060F81B73C18B5F5E020F1E434100000060F81B73C16A3E74A0E2925F41003AB24D736DED406A3E74A0E2925F41003AB24D736DED408B5F5E020F1E434100000060F81B73C18B5F5E020F1E4341'::geometry) | |
Total runtime: 2.923 ms | |
(6 rows) | |
/* (SELECT * FROM polygons9 WHERE the_geom && !bbox! and iucn_category_id = 2) AS GEOM */ | |
tiledb=# explain analyze SELECT AsBinary("the_geom") AS geom from | |
(SELECT * FROM polygons9 WHERE the_geom && SetSRID('BOX3D(-20037510 2505758.018505042,60267.60323439911 8276874.507094959)'::box3d, 900913) and iucn_category_id = 2) AS GEOM ; | |
QUERY PLAN | |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | |
Bitmap Heap Scan on polygons9 (cost=19.85..264.07 rows=8 width=93117) (actual time=0.410..1.971 rows=21 loops=1) | |
Recheck Cond: (the_geom && '010300002031BF0D00010000000500000000000060F81B73C18B5F5E020F1E434100000060F81B73C16A3E74A0E2925F41003AB24D736DED406A3E74A0E2925F41003AB24D736DED408B5F5E020F1E434100000060F81B73C18B5F5E020F1E4341'::geometry) | |
Filter: (iucn_category_id = 2) | |
-> Bitmap Index Scan on polygons9_gist_idx (cost=0.00..19.85 rows=480 width=0) (actual time=0.305..0.305 rows=350 loops=1) | |
Index Cond: (the_geom && '010300002031BF0D00010000000500000000000060F81B73C18B5F5E020F1E434100000060F81B73C16A3E74A0E2925F41003AB24D736DED406A3E74A0E2925F41003AB24D736DED408B5F5E020F1E434100000060F81B73C18B5F5E020F1E4341'::geometry) | |
Total runtime: 2.012 ms | |
(6 rows) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment