[postgis-devel] cost of some functions (e.g. st_within) might be too high after 3ddc11483b
Tomas Vondra
tomas.vondra at 2ndquadrant.com
Thu Sep 29 05:36:33 PDT 2022
On 9/26/22 21:15, Tomas Vondra wrote:
> ...
>
> So I assume with the function cost 1000 this would still pick the right
> plan. Of course, maybe they should tune the random_page_cost a bit, in
> which case the function cost could stay higher.
>
> I'll see if I can get access to the system again and experiment a bit.
>
OK, I have access to an environment where I can reproduce this, so I did
a bunch of experiments, and it seems the plan switches somewhere between
cost 6100 and 6200.
That's higher than what I estimated above, mostly because some of the
row count estimates are higher in these plans. But there's fair amount
of volatility - the row estimates fluctuate between 300 and 1000, and
with lower values the function cost has to be lower (because LIMIT has
to fetch more rows).
The real count is ~1000, so I'd say the ~6100 cost is based on correct
input data (the original estimates were low, which means 1000 cost was
misleading).
I have collected the plans with function costs set to default (10k),
6100 and 6200, with various GUC settings. The script and plans
(redacted, to hide table/column names) is attached.
Two more observations:
1) The query has a bunch of other conditions, that are usually evaluated
as a filter (not index condition). Those tend to be cheap, but may
eliminate some of the rows, so that the ORDER BY + LIMIT may need to
fetch more rows.
2) I did try to reduce random_page_cost, but that actually has the
opposite effect - with r_p_c=2 I have to go all the way to 4700 for the
plan to change.
regards
--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
-------------- next part --------------
A non-text attachment was scrubbed...
Name: script.sql
Type: application/sql
Size: 773 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-devel/attachments/20220929/d46d3a9a/attachment-0001.bin>
-------------- next part --------------
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=205201.98..205225.31 rows=200 width=72) (actual time=1195.064..1197.354 rows=200 loops=1)
Output: LIST
Buffers: shared hit=110 read=21980
-> Gather Merge (cost=205201.98..205284.11 rows=704 width=72) (actual time=1195.061..1197.031 rows=200 loops=1)
Output: LIST
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=110 read=21980
-> Sort (cost=204201.95..204202.83 rows=352 width=72) (actual time=1188.492..1188.638 rows=160 loops=3)
Output: LIST
Sort Key: (('X'::geometry <-> (sc.COLUMN)::geometry))
Sort Method: quicksort Memory: 80kB
Worker 0: Sort Method: quicksort Memory: 66kB
Worker 1: Sort Method: quicksort Memory: 67kB
Buffers: shared hit=110 read=21980
Worker 0: actual time=1185.655..1185.828 rows=200 loops=1
Buffers: shared hit=11 read=1843
Worker 1: actual time=1185.639..1185.804 rows=200 loops=1
Buffers: shared hit=17 read=1873
-> Parallel Bitmap Heap Scan on public.my_table sc (cost=122541.70..204187.06 rows=352 width=72) (actual time=1164.811..1187.766 rows=311 loops=3)
Output: LIST
Recheck Cond: ((sc.COLUMN >= 'X'::timestamp without time zone) AND (sc.COLUMN <= 'X'::timestamp without time zone))
Rows Removed by Index Recheck: 2345
Filter: ((sc.COLUMN = ANY ('X'::smallint[])) AND (((sc.COLUMN >= 0) AND (sc.COLUMN <= 1000000)) OR (sc.COLUMN IS NULL)) AND (((sc.COLUMN >= 0) AND (sc.COLUMN <= 500000000)) OR (sc.COLUMN IS NULL)) AND (((sc.COLUMN >= 1601) AND (sc.COLUMN <= 2022)) OR (sc.COLUMN IS NULL)) AND (((sc.COLUMN >= 0) AND (sc.COLUMN <= 99)) OR (sc.COLUMN IS NULL)) AND (((sc.COLUMN >= 0) AND (sc.COLUMN <= 99)) OR (sc.COLUMN IS NULL)) AND (sc.COLUMN = ANY ('X'::integer[])) AND (sc.COLUMN = ANY ('X'::integer[])) AND st_within((sc.COLUMN)::geometry, 'X'::geometry))
Rows Removed by Filter: 102
Heap Blocks: exact=2676
Buffers: shared hit=96 read=21980
Worker 0: actual time=1161.823..1184.955 rows=277 loops=1
Buffers: shared hit=4 read=1843
Worker 1: actual time=1161.802..1184.957 rows=286 loops=1
Buffers: shared hit=10 read=1873
-> BitmapAnd (cost=122541.70..122541.70 rows=5761 width=0) (actual time=1166.333..1166.338 rows=0 loops=1)
Buffers: shared hit=78 read=15592
-> Bitmap Index Scan on my_index (cost=0.00..1667.01 rows=40259 width=0) (actual time=8.897..8.898 rows=10481 loops=1)
Index Cond: ((sc.COLUMN)::geometry @ 'X'::geometry)
Buffers: shared hit=78 read=131
-> Bitmap Index Scan on my_index (cost=0.00..120874.02 rows=5760945 width=0) (actual time=1154.150..1154.151 rows=5657356 loops=1)
Index Cond: ((sc.COLUMN >= 'X'::timestamp without time zone) AND (sc.COLUMN <= 'X'::timestamp without time zone))
Buffers: shared read=15461
Planning Time: 7.232 ms
Execution Time: 1197.981 ms
(41 rows)
SET
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.42..276033.83 rows=200 width=72) (actual time=2.893..15.489 rows=200 loops=1)
Output: LIST
Buffers: shared hit=1566 read=473
-> Index Scan using my_index on public.my_table sc (cost=0.42..1166241.59 rows=845 width=72) (actual time=2.891..15.184 rows=200 loops=1)
Output: LIST
Index Cond: ((sc.COLUMN)::geometry @ 'X'::geometry)
Order By: ((sc.COLUMN)::geometry <-> 'X'::geometry)
Filter: ((sc.COLUMN >= 'X'::timestamp without time zone) AND (sc.COLUMN <= 'X'::timestamp without time zone) AND (sc.COLUMN = ANY ('X'::smallint[])) AND (((sc.COLUMN >= 0) AND (sc.COLUMN <= 1000000)) OR (sc.COLUMN IS NULL)) AND (((sc.COLUMN >= 0) AND (sc.COLUMN <= 500000000)) OR (sc.COLUMN IS NULL)) AND (((sc.COLUMN >= 1601) AND (sc.COLUMN <= 2022)) OR (sc.COLUMN IS NULL)) AND (((sc.COLUMN >= 0) AND (sc.COLUMN <= 99)) OR (sc.COLUMN IS NULL)) AND (((sc.COLUMN >= 0) AND (sc.COLUMN <= 99)) OR (sc.COLUMN IS NULL)) AND (sc.COLUMN = ANY ('X'::integer[])) AND (sc.COLUMN = ANY ('X'::integer[])) AND st_within((sc.COLUMN)::geometry, 'X'::geometry))
Rows Removed by Filter: 1765
Buffers: shared hit=1566 read=473
Planning Time: 0.728 ms
Execution Time: 15.925 ms
(12 rows)
SET
SET
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=289053.08..289053.58 rows=200 width=72) (actual time=1156.985..1157.432 rows=200 loops=1)
Output: LIST
Buffers: shared hit=244 read=21832
-> Sort (cost=289053.08..289055.20 rows=845 width=72) (actual time=1156.982..1157.137 rows=200 loops=1)
Output: LIST
Sort Key: (('X'::geometry <-> (sc.COLUMN)::geometry))
Sort Method: top-N heapsort Memory: 76kB
Buffers: shared hit=244 read=21832
-> Bitmap Heap Scan on public.my_table sc (cost=122541.70..289016.56 rows=845 width=72) (actual time=1104.952..1155.002 rows=934 loops=1)
Output: LIST
Recheck Cond: ((sc.COLUMN >= 'X'::timestamp without time zone) AND (sc.COLUMN <= 'X'::timestamp without time zone))
Rows Removed by Index Recheck: 7035
Filter: ((sc.COLUMN = ANY ('X'::smallint[])) AND (((sc.COLUMN >= 0) AND (sc.COLUMN <= 1000000)) OR (sc.COLUMN IS NULL)) AND (((sc.COLUMN >= 0) AND (sc.COLUMN <= 500000000)) OR (sc.COLUMN IS NULL)) AND (((sc.COLUMN >= 1601) AND (sc.COLUMN <= 2022)) OR (sc.COLUMN IS NULL)) AND (((sc.COLUMN >= 0) AND (sc.COLUMN <= 99)) OR (sc.COLUMN IS NULL)) AND (((sc.COLUMN >= 0) AND (sc.COLUMN <= 99)) OR (sc.COLUMN IS NULL)) AND (sc.COLUMN = ANY ('X'::integer[])) AND (sc.COLUMN = ANY ('X'::integer[])) AND st_within((sc.COLUMN)::geometry, 'X'::geometry))
Rows Removed by Filter: 305
Heap Blocks: exact=6406
Buffers: shared hit=244 read=21832
-> BitmapAnd (cost=122541.70..122541.70 rows=5761 width=0) (actual time=1100.806..1100.812 rows=0 loops=1)
Buffers: shared hit=78 read=15592
-> Bitmap Index Scan on my_index (cost=0.00..1667.01 rows=40259 width=0) (actual time=7.258..7.260 rows=10481 loops=1)
Index Cond: ((sc.COLUMN)::geometry @ 'X'::geometry)
Buffers: shared hit=78 read=131
-> Bitmap Index Scan on my_index (cost=0.00..120874.02 rows=5760945 width=0) (actual time=1090.366..1090.367 rows=5657356 loops=1)
Index Cond: ((sc.COLUMN >= 'X'::timestamp without time zone) AND (sc.COLUMN <= 'X'::timestamp without time zone))
Buffers: shared read=15461
Planning Time: 0.637 ms
Execution Time: 1158.707 ms
(26 rows)
SET
SET
RESET
-------------- next part --------------
ALTER FUNCTION
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=182398.02..182421.35 rows=200 width=72) (actual time=1134.850..1137.122 rows=200 loops=1)
Output: LIST
Buffers: shared hit=170 read=21920
-> Gather Merge (cost=182398.02..182480.16 rows=704 width=72) (actual time=1134.847..1136.821 rows=200 loops=1)
Output: LIST
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=170 read=21920
-> Sort (cost=181397.99..181398.87 rows=352 width=72) (actual time=1126.837..1126.979 rows=164 loops=3)
Output: LIST
Sort Key: (('X'::geometry <-> (sc.COLUMN)::geometry))
Sort Method: quicksort Memory: 80kB
Worker 0: Sort Method: quicksort Memory: 68kB
Worker 1: Sort Method: quicksort Memory: 65kB
Buffers: shared hit=170 read=21920
Worker 0: actual time=1123.176..1123.356 rows=200 loops=1
Buffers: shared hit=35 read=1816
Worker 1: actual time=1123.180..1123.347 rows=200 loops=1
Buffers: shared hit=26 read=1849
-> Parallel Bitmap Heap Scan on public.my_table sc (cost=122541.70..181383.11 rows=352 width=72) (actual time=1103.404..1126.090 rows=311 loops=3)
Output: LIST
Recheck Cond: ((sc.COLUMN >= 'X'::timestamp without time zone) AND (sc.COLUMN <= 'X'::timestamp without time zone))
Rows Removed by Index Recheck: 2345
Filter: ((sc.COLUMN = ANY ('X'::smallint[])) AND (((sc.COLUMN >= 0) AND (sc.COLUMN <= 1000000)) OR (sc.COLUMN IS NULL)) AND (((sc.COLUMN >= 0) AND (sc.COLUMN <= 500000000)) OR (sc.COLUMN IS NULL)) AND (((sc.COLUMN >= 1601) AND (sc.COLUMN <= 2022)) OR (sc.COLUMN IS NULL)) AND (((sc.COLUMN >= 0) AND (sc.COLUMN <= 99)) OR (sc.COLUMN IS NULL)) AND (((sc.COLUMN >= 0) AND (sc.COLUMN <= 99)) OR (sc.COLUMN IS NULL)) AND (sc.COLUMN = ANY ('X'::integer[])) AND (sc.COLUMN = ANY ('X'::integer[])) AND st_within((sc.COLUMN)::geometry, 'X'::geometry))
Rows Removed by Filter: 102
Heap Blocks: exact=2694
Buffers: shared hit=158 read=21918
Worker 0: actual time=1099.641..1122.472 rows=290 loops=1
Buffers: shared hit=28 read=1816
Worker 1: actual time=1099.923..1122.478 rows=271 loops=1
Buffers: shared hit=21 read=1847
-> BitmapAnd (cost=122541.70..122541.70 rows=5761 width=0) (actual time=1106.924..1106.929 rows=0 loops=1)
Buffers: shared hit=78 read=15592
-> Bitmap Index Scan on my_index (cost=0.00..1667.01 rows=40259 width=0) (actual time=8.297..8.298 rows=10481 loops=1)
Index Cond: ((sc.COLUMN)::geometry @ 'X'::geometry)
Buffers: shared hit=78 read=131
-> Bitmap Index Scan on my_index (cost=0.00..120874.02 rows=5760945 width=0) (actual time=1094.946..1094.947 rows=5657356 loops=1)
Index Cond: ((sc.COLUMN >= 'X'::timestamp without time zone) AND (sc.COLUMN <= 'X'::timestamp without time zone))
Buffers: shared read=15461
Planning Time: 0.805 ms
Execution Time: 1137.475 ms
(41 rows)
SET
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.42..185510.64 rows=200 width=72) (actual time=2.908..15.296 rows=200 loops=1)
Output: LIST
Buffers: shared hit=1570 read=469
-> Index Scan using my_index on public.my_table sc (cost=0.42..783781.09 rows=845 width=72) (actual time=2.905..15.001 rows=200 loops=1)
Output: LIST
Index Cond: ((sc.COLUMN)::geometry @ 'X'::geometry)
Order By: ((sc.COLUMN)::geometry <-> 'X'::geometry)
Filter: ((sc.COLUMN >= 'X'::timestamp without time zone) AND (sc.COLUMN <= 'X'::timestamp without time zone) AND (sc.COLUMN = ANY ('X'::smallint[])) AND (((sc.COLUMN >= 0) AND (sc.COLUMN <= 1000000)) OR (sc.COLUMN IS NULL)) AND (((sc.COLUMN >= 0) AND (sc.COLUMN <= 500000000)) OR (sc.COLUMN IS NULL)) AND (((sc.COLUMN >= 1601) AND (sc.COLUMN <= 2022)) OR (sc.COLUMN IS NULL)) AND (((sc.COLUMN >= 0) AND (sc.COLUMN <= 99)) OR (sc.COLUMN IS NULL)) AND (((sc.COLUMN >= 0) AND (sc.COLUMN <= 99)) OR (sc.COLUMN IS NULL)) AND (sc.COLUMN = ANY ('X'::integer[])) AND (sc.COLUMN = ANY ('X'::integer[])) AND st_within((sc.COLUMN)::geometry, 'X'::geometry))
Rows Removed by Filter: 1765
Buffers: shared hit=1570 read=469
Planning Time: 0.721 ms
Execution Time: 15.731 ms
(12 rows)
SET
SET
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=234323.58..234324.08 rows=200 width=72) (actual time=1148.215..1148.635 rows=200 loops=1)
Output: LIST
Buffers: shared hit=675 read=21401
-> Sort (cost=234323.58..234325.70 rows=845 width=72) (actual time=1148.212..1148.368 rows=200 loops=1)
Output: LIST
Sort Key: (('X'::geometry <-> (sc.COLUMN)::geometry))
Sort Method: top-N heapsort Memory: 76kB
Buffers: shared hit=675 read=21401
-> Bitmap Heap Scan on public.my_table sc (cost=122541.70..234287.06 rows=845 width=72) (actual time=1100.576..1146.332 rows=934 loops=1)
Output: LIST
Recheck Cond: ((sc.COLUMN >= 'X'::timestamp without time zone) AND (sc.COLUMN <= 'X'::timestamp without time zone))
Rows Removed by Index Recheck: 7035
Filter: ((sc.COLUMN = ANY ('X'::smallint[])) AND (((sc.COLUMN >= 0) AND (sc.COLUMN <= 1000000)) OR (sc.COLUMN IS NULL)) AND (((sc.COLUMN >= 0) AND (sc.COLUMN <= 500000000)) OR (sc.COLUMN IS NULL)) AND (((sc.COLUMN >= 1601) AND (sc.COLUMN <= 2022)) OR (sc.COLUMN IS NULL)) AND (((sc.COLUMN >= 0) AND (sc.COLUMN <= 99)) OR (sc.COLUMN IS NULL)) AND (((sc.COLUMN >= 0) AND (sc.COLUMN <= 99)) OR (sc.COLUMN IS NULL)) AND (sc.COLUMN = ANY ('X'::integer[])) AND (sc.COLUMN = ANY ('X'::integer[])) AND st_within((sc.COLUMN)::geometry, 'X'::geometry))
Rows Removed by Filter: 305
Heap Blocks: exact=6406
Buffers: shared hit=675 read=21401
-> BitmapAnd (cost=122541.70..122541.70 rows=5761 width=0) (actual time=1096.259..1096.264 rows=0 loops=1)
Buffers: shared hit=78 read=15592
-> Bitmap Index Scan on my_index (cost=0.00..1667.01 rows=40259 width=0) (actual time=7.208..7.209 rows=10481 loops=1)
Index Cond: ((sc.COLUMN)::geometry @ 'X'::geometry)
Buffers: shared hit=78 read=131
-> Bitmap Index Scan on my_index (cost=0.00..120874.02 rows=5760945 width=0) (actual time=1085.961..1085.962 rows=5657356 loops=1)
Index Cond: ((sc.COLUMN >= 'X'::timestamp without time zone) AND (sc.COLUMN <= 'X'::timestamp without time zone))
Buffers: shared read=15461
Planning Time: 0.641 ms
Execution Time: 1149.897 ms
(26 rows)
SET
SET
-------------- next part --------------
ALTER FUNCTION
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.42..183128.45 rows=200 width=72) (actual time=2.885..15.375 rows=200 loops=1)
Output: LIST
Buffers: shared hit=1570 read=469
-> Index Scan using my_index on public.my_table sc (cost=0.42..773716.34 rows=845 width=72) (actual time=2.882..15.071 rows=200 loops=1)
Output: LIST
Index Cond: ((sc.COLUMN)::geometry @ 'X'::geometry)
Order By: ((sc.COLUMN)::geometry <-> 'X'::geometry)
Filter: ((sc.COLUMN >= 'X'::timestamp without time zone) AND (sc.COLUMN <= 'X'::timestamp without time zone) AND (sc.COLUMN = ANY ('X'::smallint[])) AND (((sc.COLUMN >= 0) AND (sc.COLUMN <= 1000000)) OR (sc.COLUMN IS NULL)) AND (((sc.COLUMN >= 0) AND (sc.COLUMN <= 500000000)) OR (sc.COLUMN IS NULL)) AND (((sc.COLUMN >= 1601) AND (sc.COLUMN <= 2022)) OR (sc.COLUMN IS NULL)) AND (((sc.COLUMN >= 0) AND (sc.COLUMN <= 99)) OR (sc.COLUMN IS NULL)) AND (((sc.COLUMN >= 0) AND (sc.COLUMN <= 99)) OR (sc.COLUMN IS NULL)) AND (sc.COLUMN = ANY ('X'::integer[])) AND (sc.COLUMN = ANY ('X'::integer[])) AND st_within((sc.COLUMN)::geometry, 'X'::geometry))
Rows Removed by Filter: 1765
Buffers: shared hit=1570 read=469
Planning Time: 0.753 ms
Execution Time: 15.859 ms
(12 rows)
SET
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.42..183128.45 rows=200 width=72) (actual time=2.309..11.931 rows=200 loops=1)
Output: LIST
Buffers: shared hit=2039
-> Index Scan using my_index on public.my_table sc (cost=0.42..773716.34 rows=845 width=72) (actual time=2.306..11.636 rows=200 loops=1)
Output: LIST
Index Cond: ((sc.COLUMN)::geometry @ 'X'::geometry)
Order By: ((sc.COLUMN)::geometry <-> 'X'::geometry)
Filter: ((sc.COLUMN >= 'X'::timestamp without time zone) AND (sc.COLUMN <= 'X'::timestamp without time zone) AND (sc.COLUMN = ANY ('X'::smallint[])) AND (((sc.COLUMN >= 0) AND (sc.COLUMN <= 1000000)) OR (sc.COLUMN IS NULL)) AND (((sc.COLUMN >= 0) AND (sc.COLUMN <= 500000000)) OR (sc.COLUMN IS NULL)) AND (((sc.COLUMN >= 1601) AND (sc.COLUMN <= 2022)) OR (sc.COLUMN IS NULL)) AND (((sc.COLUMN >= 0) AND (sc.COLUMN <= 99)) OR (sc.COLUMN IS NULL)) AND (((sc.COLUMN >= 0) AND (sc.COLUMN <= 99)) OR (sc.COLUMN IS NULL)) AND (sc.COLUMN = ANY ('X'::integer[])) AND (sc.COLUMN = ANY ('X'::integer[])) AND st_within((sc.COLUMN)::geometry, 'X'::geometry))
Rows Removed by Filter: 1765
Buffers: shared hit=2039
Planning Time: 0.631 ms
Execution Time: 12.353 ms
(12 rows)
SET
SET
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=232883.33..232883.83 rows=200 width=72) (actual time=1155.040..1155.459 rows=200 loops=1)
Output: LIST
Buffers: shared hit=1516 read=20560
-> Sort (cost=232883.33..232885.45 rows=845 width=72) (actual time=1155.037..1155.191 rows=200 loops=1)
Output: LIST
Sort Key: (('X'::geometry <-> (sc.COLUMN)::geometry))
Sort Method: top-N heapsort Memory: 76kB
Buffers: shared hit=1516 read=20560
-> Bitmap Heap Scan on public.my_table sc (cost=122541.70..232846.81 rows=845 width=72) (actual time=1110.625..1153.187 rows=934 loops=1)
Output: LIST
Recheck Cond: ((sc.COLUMN >= 'X'::timestamp without time zone) AND (sc.COLUMN <= 'X'::timestamp without time zone))
Rows Removed by Index Recheck: 7035
Filter: ((sc.COLUMN = ANY ('X'::smallint[])) AND (((sc.COLUMN >= 0) AND (sc.COLUMN <= 1000000)) OR (sc.COLUMN IS NULL)) AND (((sc.COLUMN >= 0) AND (sc.COLUMN <= 500000000)) OR (sc.COLUMN IS NULL)) AND (((sc.COLUMN >= 1601) AND (sc.COLUMN <= 2022)) OR (sc.COLUMN IS NULL)) AND (((sc.COLUMN >= 0) AND (sc.COLUMN <= 99)) OR (sc.COLUMN IS NULL)) AND (((sc.COLUMN >= 0) AND (sc.COLUMN <= 99)) OR (sc.COLUMN IS NULL)) AND (sc.COLUMN = ANY ('X'::integer[])) AND (sc.COLUMN = ANY ('X'::integer[])) AND st_within((sc.COLUMN)::geometry, 'X'::geometry))
Rows Removed by Filter: 305
Heap Blocks: exact=6406
Buffers: shared hit=1516 read=20560
-> BitmapAnd (cost=122541.70..122541.70 rows=5761 width=0) (actual time=1106.669..1106.674 rows=0 loops=1)
Buffers: shared hit=78 read=15592
-> Bitmap Index Scan on my_index (cost=0.00..1667.01 rows=40259 width=0) (actual time=7.171..7.172 rows=10481 loops=1)
Index Cond: ((sc.COLUMN)::geometry @ 'X'::geometry)
Buffers: shared hit=78 read=131
-> Bitmap Index Scan on my_index (cost=0.00..120874.02 rows=5760945 width=0) (actual time=1096.444..1096.445 rows=5657356 loops=1)
Index Cond: ((sc.COLUMN >= 'X'::timestamp without time zone) AND (sc.COLUMN <= 'X'::timestamp without time zone))
Buffers: shared read=15461
Planning Time: 0.617 ms
Execution Time: 1156.726 ms
(26 rows)
SET
SET
RESET
More information about the postgis-devel
mailing list