Skip to content

Instantly share code, notes, and snippets.

@springmeyer
Created January 8, 2011 00:47
Show Gist options
  • Save springmeyer/770381 to your computer and use it in GitHub Desktop.
Save springmeyer/770381 to your computer and use it in GitHub Desktop.
benefit to having !bbox! query manually placed inside subselect beside other WHERE?
/* (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