[prev in list] [next in list] [prev in thread] [next in thread]
List: postgis-users
Subject: [postgis-users] Improving query planner estimates for geo column?
From: Mike Krieger <mikekrieger () gmail ! com>
Date: 2011-04-20 2:13:08
Message-ID: 454B1F8B3C7248D2959EF597772D9657 () gmail ! com
[Download RAW message or body]
[Attachment #2 (multipart/alternative)]
Hi all,
We're running into a problem query where the query planner is underestimating the \
rows that will be returned from a geo query by a large amount (#1). The intent of the \
query is, "what are the most recent entries in this area taken in the last N hours, \
sorted by date?"
The issue is that the query planner estimates just 8000 rows coming back from the geo \
part of the query, when in fact over 200000 rows are returned (query analyze at #2).
If we use a shorter time-range (12 hours), the query planner has a 'better' plan and \
instead BitmapAnds the two indexes (#3).
My questions are:
1) Is there any way to encourage the query planner to not try and grab all 280k \
matching rows from its index? 2) Is there any index we can create (compound?) that \
could make this query easier for the DB to run?
I'm running PSQL 9.0 and PostGIS 1.5.2, and the tables have been recently VACUUM \
ANALYZED.
Thanks!
Mike
===
#1
SELECT * FROM entries WHERE
ST_Intersects("entries"."pnt", \
ST_GeomFromEWKB(E'\\x0103000020e61000000100000021000000c976be9f1a975ec0f8c264aa60e4424 \
00cde5c5d2a975ec0e3f0940721e34240bbf15cfb58975ec0e71754adede142401fc220afa4975ec0b0395 \
96bd2e04240106ae78f0a985ec01f435024dadf424058e56bb386985ec03ef3c2620edf424074546954149 \
95ec075523bfb76de4240f1c08901ae995ec0192b3bbf19de4240fca9f1d24d9a5ec0925cfe43fadd42400 \
79359a4ed9a5ec0192b3bbf19de424084ff7951879b5ec075523bfb76de4240a06e77f2149c5ec03ef3c26 \
20edf4240e8e9fb15919c5ec01f435024dadf4240d991c2f6f69c5ec0b039596bd2e042403d6286aa429d5 \
ec0e71754adede14240ec758648719d5ec0e3f0940721e342402fdd2406819d5ec0f8c264aa60e44240ec7 \
58648719d5ec00d95344da0e542403d6286aa429d5ec0096e75a7d3e64240d991c2f6f69c5ec0404c70e9e \
ee74240e8e9fb15919c5ec0d1427930e7e84240a06e77f2149c5ec0b29206f2b2e9424084ff7951879b5ec \
07b338e594aea4240079359a4ed9a5ec0d75a8e95a7ea4240fca9f1d24d9a5ec05e29cb10c7ea4240f1c08 \
901ae995ec0d75a8e95a7ea42407454695414995ec07b338e594aea424058e56bb386985ec0b29206f2b2e94240106ae78
f0a985ec
0d1427930e7e842401fc220afa4975ec0404c70e9eee74240bbf15cfb58975ec0096e75a7d3e642400cde5c5d2a975ec00d95344da0e54240c976be9f1a975ec0f8c264aa60e44240'::bytea))
AND created_at > now() - interval '72 hours'
ORDER BY "entries"."id" DESC
LIMIT 21;
===
#2
Limit (cost=32780.29..32780.34 rows=21 width=198) (actual time=660.869..660.906 \
rows=21 loops=1)
-> Sort (cost=32780.29..32780.49 rows=81 width=198) (actual time=660.867..660.879 \
rows=21 loops=1) Sort Key: id
Sort Method: top-N heapsort Memory: 30kB
-> Bitmap Heap Scan on entries (cost=385.81..32778.11 rows=81 width=198) (actual \
time=630.004..654.892 rows=5945 loops=1) Recheck Cond: (pnt && \
'0103000020E61000000100000021000000C976BE9F1A975EC0F8C264AA60E442400CDE5C5D2A975EC0E3F \
0940721E34240BBF15CFB58975EC0E71754ADEDE142401FC220AFA4975EC0B039596BD2E04240106AE78F0 \
A985EC01F435024DADF424058E56BB386985EC03EF3C2620EDF42407454695414995EC075523BFB76DE424 \
0F1C08901AE995EC0192B3BBF19DE4240FCA9F1D24D9A5EC0925CFE43FADD4240079359A4ED9A5EC0192B3 \
BBF19DE424084FF7951879B5EC075523BFB76DE4240A06E77F2149C5EC03EF3C2620EDF4240E8E9FB15919 \
C5EC01F435024DADF4240D991C2F6F69C5EC0B039596BD2E042403D6286AA429D5EC0E71754ADEDE14240E \
C758648719D5EC0E3F0940721E342402FDD2406819D5EC0F8C264AA60E44240EC758648719D5EC00D95344 \
DA0E542403D6286AA429D5EC0096E75A7D3E64240D991C2F6F69C5EC0404C70E9EEE74240E8E9FB15919C5 \
EC0D1427930E7E84240A06E77F2149C5EC0B29206F2B2E9424084FF7951879B5EC07B338E594AEA4240079 \
359A4ED9A5EC0D75A8E95A7EA4240FCA9F1D24D9A5EC05E29CB10C7EA4240F1C08901AE995EC0D75A8E95A \
7EA42407454695414995EC07B338E594AEA424058E56BB386985EC0B29206F2B2E94240106AE78F0A985EC0D1427930E7E842401FC2
20AFA497
5EC0404C70E9EEE74240BBF15CFB58975EC0096E75A7D3E642400CDE5C5D2A975EC00D95344DA0E54240C976BE9F1A975EC0F8C264AA60E44240'::geometry)
Filter: ((taken_at > (now() - '72:00:00'::interval)) AND _st_intersects(pnt, \
'0103000020E61000000100000021000000C976BE9F1A975EC0F8C264AA60E442400CDE5C5D2A975EC0E3F \
0940721E34240BBF15CFB58975EC0E71754ADEDE142401FC220AFA4975EC0B039596BD2E04240106AE78F0 \
A985EC01F435024DADF424058E56BB386985EC03EF3C2620EDF42407454695414995EC075523BFB76DE424 \
0F1C08901AE995EC0192B3BBF19DE4240FCA9F1D24D9A5EC0925CFE43FADD4240079359A4ED9A5EC0192B3 \
BBF19DE424084FF7951879B5EC075523BFB76DE4240A06E77F2149C5EC03EF3C2620EDF4240E8E9FB15919 \
C5EC01F435024DADF4240D991C2F6F69C5EC0B039596BD2E042403D6286AA429D5EC0E71754ADEDE14240E \
C758648719D5EC0E3F0940721E342402FDD2406819D5EC0F8C264AA60E44240EC758648719D5EC00D95344 \
DA0E542403D6286AA429D5EC0096E75A7D3E64240D991C2F6F69C5EC0404C70E9EEE74240E8E9FB15919C5 \
EC0D1427930E7E84240A06E77F2149C5EC0B29206F2B2E9424084FF7951879B5EC07B338E594AEA4240079 \
359A4ED9A5EC0D75A8E95A7EA4240FCA9F1D24D9A5EC05E29CB10C7EA4240F1C08901AE995EC0D75A8E95A7EA42407454695414995EC07B338E594AEA424058E56BB386985
EC0B2920
6F2B2E94240106AE78F0A985EC0D1427930E7E842401FC220AFA4975EC0404C70E9EEE74240BBF15CFB589 \
75EC0096E75A7D3E642400CDE5C5D2A975EC00D95344DA0E54240C976BE9F1A975EC0F8C264AA60E44240'::geometry))
-> Bitmap Index Scan on entries_pnt_id (cost=0.00..385.79 rows=8118 width=0) (actual \
time=128.146..128.146 rows=206976 loops=1) Index Cond: (pnt && \
'0103000020E61000000100000021000000C976BE9F1A975EC0F8C264AA60E442400CDE5C5D2A975EC0E3F \
0940721E34240BBF15CFB58975EC0E71754ADEDE142401FC220AFA4975EC0B039596BD2E04240106AE78F0 \
A985EC01F435024DADF424058E56BB386985EC03EF3C2620EDF42407454695414995EC075523BFB76DE424 \
0F1C08901AE995EC0192B3BBF19DE4240FCA9F1D24D9A5EC0925CFE43FADD4240079359A4ED9A5EC0192B3 \
BBF19DE424084FF7951879B5EC075523BFB76DE4240A06E77F2149C5EC03EF3C2620EDF4240E8E9FB15919 \
C5EC01F435024DADF4240D991C2F6F69C5EC0B039596BD2E042403D6286AA429D5EC0E71754ADEDE14240E \
C758648719D5EC0E3F0940721E342402FDD2406819D5EC0F8C264AA60E44240EC758648719D5EC00D95344 \
DA0E542403D6286AA429D5EC0096E75A7D3E64240D991C2F6F69C5EC0404C70E9EEE74240E8E9FB15919C5 \
EC0D1427930E7E84240A06E77F2149C5EC0B29206F2B2E9424084FF7951879B5EC07B338E594AEA4240079 \
359A4ED9A5EC0D75A8E95A7EA4240FCA9F1D24D9A5EC05E29CB10C7EA4240F1C08901AE995EC0D75A8E95A \
7EA42407454695414995EC07B338E594AEA424058E56BB386985EC0B29206F2B2E94240106AE78F0A985EC0D1427930E7E842401FC220
AFA4975E
C0404C70E9EEE74240BBF15CFB58975EC0096E75A7D3E642400CDE5C5D2A975EC00D95344DA0E54240C976BE9F1A975EC0F8C264AA60E44240'::geometry)
660 ms
===
#3
Limit (cost=8512.70..8512.73 rows=12 width=198) (actual time=253.835..253.874 rows=21 \
loops=1)
-> Sort (cost=8512.70..8512.73 rows=12 width=198) (actual time=253.833..253.847 \
rows=21 loops=1) Sort Key: id
Sort Method: top-N heapsort Memory: 30kB
-> Bitmap Heap Scan on entries (cost=8355.16..8512.49 rows=12 width=198) (actual \
time=234.900..252.505 rows=1014 loops=1) Recheck Cond: ((pnt && \
'0103000020E61000000100000021000000C976BE9F1A975EC0F8C264AA60E442400CDE5C5D2A975EC0E3F \
0940721E34240BBF15CFB58975EC0E71754ADEDE142401FC220AFA4975EC0B039596BD2E04240106AE78F0 \
A985EC01F435024DADF424058E56BB386985EC03EF3C2620EDF42407454695414995EC075523BFB76DE424 \
0F1C08901AE995EC0192B3BBF19DE4240FCA9F1D24D9A5EC0925CFE43FADD4240079359A4ED9A5EC0192B3 \
BBF19DE424084FF7951879B5EC075523BFB76DE4240A06E77F2149C5EC03EF3C2620EDF4240E8E9FB15919 \
C5EC01F435024DADF4240D991C2F6F69C5EC0B039596BD2E042403D6286AA429D5EC0E71754ADEDE14240E \
C758648719D5EC0E3F0940721E342402FDD2406819D5EC0F8C264AA60E44240EC758648719D5EC00D95344 \
DA0E542403D6286AA429D5EC0096E75A7D3E64240D991C2F6F69C5EC0404C70E9EEE74240E8E9FB15919C5 \
EC0D1427930E7E84240A06E77F2149C5EC0B29206F2B2E9424084FF7951879B5EC07B338E594AEA4240079 \
359A4ED9A5EC0D75A8E95A7EA4240FCA9F1D24D9A5EC05E29CB10C7EA4240F1C08901AE995EC0D75A8E95A \
7EA42407454695414995EC07B338E594AEA424058E56BB386985EC0B29206F2B2E94240106AE78F0A985EC0D1427930E7E842401FC
220AFA49
75EC0404C70E9EEE74240BBF15CFB58975EC0096E75A7D3E642400CDE5C5D2A975EC00D95344DA0E54240C976BE9F1A975EC0F8C264AA60E44240'::geometry) \
AND (taken_at > (now() - '12:00:00'::interval))) Filter: _st_intersects(pnt, \
'0103000020E61000000100000021000000C976BE9F1A975EC0F8C264AA60E442400CDE5C5D2A975EC0E3F \
0940721E34240BBF15CFB58975EC0E71754ADEDE142401FC220AFA4975EC0B039596BD2E04240106AE78F0 \
A985EC01F435024DADF424058E56BB386985EC03EF3C2620EDF42407454695414995EC075523BFB76DE424 \
0F1C08901AE995EC0192B3BBF19DE4240FCA9F1D24D9A5EC0925CFE43FADD4240079359A4ED9A5EC0192B3 \
BBF19DE424084FF7951879B5EC075523BFB76DE4240A06E77F2149C5EC03EF3C2620EDF4240E8E9FB15919 \
C5EC01F435024DADF4240D991C2F6F69C5EC0B039596BD2E042403D6286AA429D5EC0E71754ADEDE14240E \
C758648719D5EC0E3F0940721E342402FDD2406819D5EC0F8C264AA60E44240EC758648719D5EC00D95344 \
DA0E542403D6286AA429D5EC0096E75A7D3E64240D991C2F6F69C5EC0404C70E9EEE74240E8E9FB15919C5 \
EC0D1427930E7E84240A06E77F2149C5EC0B29206F2B2E9424084FF7951879B5EC07B338E594AEA4240079 \
359A4ED9A5EC0D75A8E95A7EA4240FCA9F1D24D9A5EC05E29CB10C7EA4240F1C08901AE995EC0D75A8E95A \
7EA42407454695414995EC07B338E594AEA424058E56BB386985EC0B29206F2B2E94240106AE78F0A985EC0D1427930E7E842
401FC220
AFA4975EC0404C70E9EEE74240BBF15CFB58975EC0096E75A7D3E642400CDE5C5D2A975EC00D95344DA0E54240C976BE9F1A975EC0F8C264AA60E44240'::geometry)
-> BitmapAnd (cost=8355.16..8355.16 rows=37 width=0) (actual time=234.048..234.048 \
rows=0 loops=1)
-> Bitmap Index Scan on entries_pnt_id (cost=0.00..385.79 rows=8118 width=0) (actual \
time=149.039..149.039 rows=206987 loops=1) Index Cond: (pnt && \
'0103000020E61000000100000021000000C976BE9F1A975EC0F8C264AA60E442400CDE5C5D2A975EC0E3F \
0940721E34240BBF15CFB58975EC0E71754ADEDE142401FC220AFA4975EC0B039596BD2E04240106AE78F0 \
A985EC01F435024DADF424058E56BB386985EC03EF3C2620EDF42407454695414995EC075523BFB76DE424 \
0F1C08901AE995EC0192B3BBF19DE4240FCA9F1D24D9A5EC0925CFE43FADD4240079359A4ED9A5EC0192B3 \
BBF19DE424084FF7951879B5EC075523BFB76DE4240A06E77F2149C5EC03EF3C2620EDF4240E8E9FB15919 \
C5EC01F435024DADF4240D991C2F6F69C5EC0B039596BD2E042403D6286AA429D5EC0E71754ADEDE14240E \
C758648719D5EC0E3F0940721E342402FDD2406819D5EC0F8C264AA60E44240EC758648719D5EC00D95344 \
DA0E542403D6286AA429D5EC0096E75A7D3E64240D991C2F6F69C5EC0404C70E9EEE74240E8E9FB15919C5 \
EC0D1427930E7E84240A06E77F2149C5EC0B29206F2B2E9424084FF7951879B5EC07B338E594AEA4240079 \
359A4ED9A5EC0D75A8E95A7EA4240FCA9F1D24D9A5EC05E29CB10C7EA4240F1C08901AE995EC0D75A8E95A \
7EA42407454695414995EC07B338E594AEA424058E56BB386985EC0B29206F2B2E94240106AE78F0A985EC0D1427930E7E842401FC220
AFA4975E
C0404C70E9EEE74240BBF15CFB58975EC0096E75A7D3E642400CDE5C5D2A975EC00D95344DA0E54240C976BE9F1A975EC0F8C264AA60E44240'::geometry)
-> Bitmap Index Scan on entries_created_at (cost=0.00..7969.12 rows=256497 width=0) \
(actual time=42.857..42.857 rows=270092 loops=1) Index Cond: (taken_at > (now() - \
'12:00:00'::interval)) Total runtime: 258.885 ms
[Attachment #5 (text/html)]
<div>
<div>Hi all,</div><div><span></span></div><div><span><br></span></div><div><span>We're \
running into a problem query where the query planner is underestimating the rows that \
will be returned from a geo query by a large amount (#1). The intent of the query is, \
"what are the most recent entries in this area taken in the last N hours, sorted by \
date?"</span></div><div><span><br></span></div><div><span><div>The issue is that the \
query planner estimates just 8000 rows coming back from the geo part of the query, \
when in fact over 200000 rows are returned (query analyze at \
#2).</div><div><br></div><div>If we use a shorter time-range (12 hours), the query \
planner has a 'better' plan and instead BitmapAnds the two indexes \
(#3).</div><div><br></div><div>My questions are:</div><div>1) Is there any way to \
encourage the query planner to not try and grab all 280k matching rows from its \
index?</div><div>2) Is there any index we can create (compound?) that could make this \
query easier for the DB to run?</div><div><br></div><div>I'm running PSQL 9.0 and \
PostGIS 1.5.2, and the tables have been recently VACUUM \
ANALYZED.</div><div><br></div><div>Thanks!</div><div>Mike</div><div><br></div><div><br></div><div>===</div><div>#1</div><div><br></div><div><div>SELECT \
* FROM entries WHERE </div><div> \
</div><div> \
ST_Intersects("entries"."pnt", \
ST_GeomFromEWKB(E'\\x0103000020e61000000100000021000000c976be9f1a975ec0f8c264aa60e4424 \
00cde5c5d2a975ec0e3f0940721e34240bbf15cfb58975ec0e71754adede142401fc220afa4975ec0b0395 \
96bd2e04240106ae78f0a985ec01f435024dadf424058e56bb386985ec03ef3c2620edf424074546954149 \
95ec075523bfb76de4240f1c08901ae995ec0192b3bbf19de4240fca9f1d24d9a5ec0925cfe43fadd42400 \
79359a4ed9a5ec0192b3bbf19de424084ff7951879b5ec075523bfb76de4240a06e77f2149c5ec03ef3c26 \
20edf4240e8e9fb15919c5ec01f435024dadf4240d991c2f6f69c5ec0b039596bd2e042403d6286aa429d5 \
ec0e71754adede14240ec758648719d5ec0e3f0940721e342402fdd2406819d5ec0f8c264aa60e44240ec7 \
58648719d5ec00d95344da0e542403d6286aa429d5ec0096e75a7d3e64240d991c2f6f69c5ec0404c70e9e \
ee74240e8e9fb15919c5ec0d1427930e7e84240a06e77f2149c5ec0b29206f2b2e9424084ff7951879b5ec \
07b338e594aea4240079359a4ed9a5ec0d75a8e95a7ea4240fca9f1d24d9a5ec05e29cb10c7ea4240f1c08 \
901ae995ec0d75a8e95a7ea42407454695414995ec07b338e594aea424058e56bb386985ec0b29206f2b2e \
94240106ae78f0a985ec0d1427930e7e842401fc220afa4975ec0404c70e9eee74240bbf15cfb58975ec00 \
96e75a7d3e642400cde5c5d2a975ec00d95344da0e54240c976be9f1a975ec0f8c264aa60e44240'::bytea))</div><div> \
\
AND created_at > now() - interval \
'72 hours'</div><div> \
ORDER BY "entries"."id" DESC</div><div> \
LIMIT \
21;</div><div><br></div><div>===</div><div>#2</div></div><div><br></div><div><br></div><div><div><div><div> Limit \
(cost=32780.29..32780.34 rows=21 width=198) (actual time=660.869..660.906 \
rows=21 loops=1)</div><div> -> Sort \
(cost=32780.29..32780.49 rows=81 width=198) (actual time=660.867..660.879 \
rows=21 loops=1)</div><div> Sort Key: \
id</div><div> Sort Method: top-N heapsort \
Memory: 30kB</div><div> -> Bitmap \
Heap Scan on entries (cost=385.81..32778.11 rows=81 width=198) (actual \
time=630.004..654.892 rows=5945 loops=1)</div><div> \
Recheck Cond: (pnt && \
'0103000020E61000000100000021000000C976BE9F1A975EC0F8C264AA60E442400CDE5C5D2A975EC0E3F \
0940721E34240BBF15CFB58975EC0E71754ADEDE142401FC220AFA4975EC0B039596BD2E04240106AE78F0 \
A985EC01F435024DADF424058E56BB386985EC03EF3C2620EDF42407454695414995EC075523BFB76DE424 \
0F1C08901AE995EC0192B3BBF19DE4240FCA9F1D24D9A5EC0925CFE43FADD4240079359A4ED9A5EC0192B3 \
BBF19DE424084FF7951879B5EC075523BFB76DE4240A06E77F2149C5EC03EF3C2620EDF4240E8E9FB15919 \
C5EC01F435024DADF4240D991C2F6F69C5EC0B039596BD2E042403D6286AA429D5EC0E71754ADEDE14240E \
C758648719D5EC0E3F0940721E342402FDD2406819D5EC0F8C264AA60E44240EC758648719D5EC00D95344 \
DA0E542403D6286AA429D5EC0096E75A7D3E64240D991C2F6F69C5EC0404C70E9EEE74240E8E9FB15919C5 \
EC0D1427930E7E84240A06E77F2149C5EC0B29206F2B2E9424084FF7951879B5EC07B338E594AEA4240079 \
359A4ED9A5EC0D75A8E95A7EA4240FCA9F1D24D9A5EC05E29CB10C7EA4240F1C08901AE995EC0D75A8E95A \
7EA42407454695414995EC07B338E594AEA424058E56BB386985EC0B29206F2B2E94240106AE78F0A985EC \
0D1427930E7E842401FC220AFA4975EC0404C70E9EEE74240BBF15CFB58975EC0096E75A7D3E642400CDE5 \
C5D2A975EC00D95344DA0E54240C976BE9F1A975EC0F8C264AA60E44240'::geometry)</div><div> \
Filter: ((taken_at > (now() - \
'72:00:00'::interval)) AND _st_intersects(pnt, \
'0103000020E61000000100000021000000C976BE9F1A975EC0F8C264AA60E442400CDE5C5D2A975EC0E3F \
0940721E34240BBF15CFB58975EC0E71754ADEDE142401FC220AFA4975EC0B039596BD2E04240106AE78F0 \
A985EC01F435024DADF424058E56BB386985EC03EF3C2620EDF42407454695414995EC075523BFB76DE424 \
0F1C08901AE995EC0192B3BBF19DE4240FCA9F1D24D9A5EC0925CFE43FADD4240079359A4ED9A5EC0192B3 \
BBF19DE424084FF7951879B5EC075523BFB76DE4240A06E77F2149C5EC03EF3C2620EDF4240E8E9FB15919 \
C5EC01F435024DADF4240D991C2F6F69C5EC0B039596BD2E042403D6286AA429D5EC0E71754ADEDE14240E \
C758648719D5EC0E3F0940721E342402FDD2406819D5EC0F8C264AA60E44240EC758648719D5EC00D95344 \
DA0E542403D6286AA429D5EC0096E75A7D3E64240D991C2F6F69C5EC0404C70E9EEE74240E8E9FB15919C5 \
EC0D1427930E7E84240A06E77F2149C5EC0B29206F2B2E9424084FF7951879B5EC07B338E594AEA4240079 \
359A4ED9A5EC0D75A8E95A7EA4240FCA9F1D24D9A5EC05E29CB10C7EA4240F1C08901AE995EC0D75A8E95A \
7EA42407454695414995EC07B338E594AEA424058E56BB386985EC0B29206F2B2E94240106AE78F0A985EC \
0D1427930E7E842401FC220AFA4975EC0404C70E9EEE74240BBF15CFB58975EC0096E75A7D3E642400CDE5 \
C5D2A975EC00D95344DA0E54240C976BE9F1A975EC0F8C264AA60E44240'::geometry))</div><div> \
-> Bitmap Index Scan \
on entries_pnt_id (cost=0.00..385.79 rows=8118 width=0) (actual \
time=128.146..128.146 rows=206976 loops=1)</div><div> \
Index Cond: (pnt && \
'0103000020E61000000100000021000000C976BE9F1A975EC0F8C264AA60E442400CDE5C5D2A975EC0E3F \
0940721E34240BBF15CFB58975EC0E71754ADEDE142401FC220AFA4975EC0B039596BD2E04240106AE78F0 \
A985EC01F435024DADF424058E56BB386985EC03EF3C2620EDF42407454695414995EC075523BFB76DE424 \
0F1C08901AE995EC0192B3BBF19DE4240FCA9F1D24D9A5EC0925CFE43FADD4240079359A4ED9A5EC0192B3 \
BBF19DE424084FF7951879B5EC075523BFB76DE4240A06E77F2149C5EC03EF3C2620EDF4240E8E9FB15919 \
C5EC01F435024DADF4240D991C2F6F69C5EC0B039596BD2E042403D6286AA429D5EC0E71754ADEDE14240E \
C758648719D5EC0E3F0940721E342402FDD2406819D5EC0F8C264AA60E44240EC758648719D5EC00D95344 \
DA0E542403D6286AA429D5EC0096E75A7D3E64240D991C2F6F69C5EC0404C70E9EEE74240E8E9FB15919C5 \
EC0D1427930E7E84240A06E77F2149C5EC0B29206F2B2E9424084FF7951879B5EC07B338E594AEA4240079 \
359A4ED9A5EC0D75A8E95A7EA4240FCA9F1D24D9A5EC05E29CB10C7EA4240F1C08901AE995EC0D75A8E95A \
7EA42407454695414995EC07B338E594AEA424058E56BB386985EC0B29206F2B2E94240106AE78F0A985EC \
0D1427930E7E842401FC220AFA4975EC0404C70E9EEE74240BBF15CFB58975EC0096E75A7D3E642400CDE5 \
C5D2A975EC00D95344DA0E54240C976BE9F1A975EC0F8C264AA60E44240'::geometry)</div><div><br></div><div>660 \
ms</div><div><br></div><div>===</div><div>#3</div><div><br></div><div><div> Limit \
(cost=8512.70..8512.73 rows=12 width=198) (actual time=253.835..253.874 rows=21 \
loops=1)</div><div> -> Sort (cost=8512.70..8512.73 rows=12 \
width=198) (actual time=253.833..253.847 rows=21 loops=1)</div><div> \
Sort Key: id</div><div> Sort \
Method: top-N heapsort Memory: 30kB</div><div> \
-> Bitmap Heap Scan on entries (cost=8355.16..8512.49 rows=12 \
width=198) (actual time=234.900..252.505 rows=1014 loops=1)</div><div> \
Recheck Cond: ((pnt && \
'0103000020E61000000100000021000000C976BE9F1A975EC0F8C264AA60E442400CDE5C5D2A975EC0E3F \
0940721E34240BBF15CFB58975EC0E71754ADEDE142401FC220AFA4975EC0B039596BD2E04240106AE78F0 \
A985EC01F435024DADF424058E56BB386985EC03EF3C2620EDF42407454695414995EC075523BFB76DE424 \
0F1C08901AE995EC0192B3BBF19DE4240FCA9F1D24D9A5EC0925CFE43FADD4240079359A4ED9A5EC0192B3 \
BBF19DE424084FF7951879B5EC075523BFB76DE4240A06E77F2149C5EC03EF3C2620EDF4240E8E9FB15919 \
C5EC01F435024DADF4240D991C2F6F69C5EC0B039596BD2E042403D6286AA429D5EC0E71754ADEDE14240E \
C758648719D5EC0E3F0940721E342402FDD2406819D5EC0F8C264AA60E44240EC758648719D5EC00D95344 \
DA0E542403D6286AA429D5EC0096E75A7D3E64240D991C2F6F69C5EC0404C70E9EEE74240E8E9FB15919C5 \
EC0D1427930E7E84240A06E77F2149C5EC0B29206F2B2E9424084FF7951879B5EC07B338E594AEA4240079 \
359A4ED9A5EC0D75A8E95A7EA4240FCA9F1D24D9A5EC05E29CB10C7EA4240F1C08901AE995EC0D75A8E95A \
7EA42407454695414995EC07B338E594AEA424058E56BB386985EC0B29206F2B2E94240106AE78F0A985EC \
0D1427930E7E842401FC220AFA4975EC0404C70E9EEE74240BBF15CFB58975EC0096E75A7D3E642400CDE5C5D2A975EC00D95344DA0E54240C976BE9F1A975EC0F8C264AA60E44240'::geometry) \
AND (taken_at > (now() - '12:00:00'::interval)))</div><div> \
Filter: _st_intersects(pnt, \
'0103000020E61000000100000021000000C976BE9F1A975EC0F8C264AA60E442400CDE5C5D2A975EC0E3F \
0940721E34240BBF15CFB58975EC0E71754ADEDE142401FC220AFA4975EC0B039596BD2E04240106AE78F0 \
A985EC01F435024DADF424058E56BB386985EC03EF3C2620EDF42407454695414995EC075523BFB76DE424 \
0F1C08901AE995EC0192B3BBF19DE4240FCA9F1D24D9A5EC0925CFE43FADD4240079359A4ED9A5EC0192B3 \
BBF19DE424084FF7951879B5EC075523BFB76DE4240A06E77F2149C5EC03EF3C2620EDF4240E8E9FB15919 \
C5EC01F435024DADF4240D991C2F6F69C5EC0B039596BD2E042403D6286AA429D5EC0E71754ADEDE14240E \
C758648719D5EC0E3F0940721E342402FDD2406819D5EC0F8C264AA60E44240EC758648719D5EC00D95344 \
DA0E542403D6286AA429D5EC0096E75A7D3E64240D991C2F6F69C5EC0404C70E9EEE74240E8E9FB15919C5 \
EC0D1427930E7E84240A06E77F2149C5EC0B29206F2B2E9424084FF7951879B5EC07B338E594AEA4240079 \
359A4ED9A5EC0D75A8E95A7EA4240FCA9F1D24D9A5EC05E29CB10C7EA4240F1C08901AE995EC0D75A8E95A \
7EA42407454695414995EC07B338E594AEA424058E56BB386985EC0B29206F2B2E94240106AE78F0A985EC \
0D1427930E7E842401FC220AFA4975EC0404C70E9EEE74240BBF15CFB58975EC0096E75A7D3E642400CDE5 \
C5D2A975EC00D95344DA0E54240C976BE9F1A975EC0F8C264AA60E44240'::geometry)</div><div> \
-> BitmapAnd \
(cost=8355.16..8355.16 rows=37 width=0) (actual time=234.048..234.048 rows=0 \
loops=1)</div><div> \
-> Bitmap Index Scan on entries_pnt_id \
(cost=0.00..385.79 rows=8118 width=0) (actual time=149.039..149.039 rows=206987 \
loops=1)</div><div> \
Index Cond: (pnt && \
'0103000020E61000000100000021000000C976BE9F1A975EC0F8C264AA60E442400CDE5C5D2A975EC0E3F \
0940721E34240BBF15CFB58975EC0E71754ADEDE142401FC220AFA4975EC0B039596BD2E04240106AE78F0 \
A985EC01F435024DADF424058E56BB386985EC03EF3C2620EDF42407454695414995EC075523BFB76DE424 \
0F1C08901AE995EC0192B3BBF19DE4240FCA9F1D24D9A5EC0925CFE43FADD4240079359A4ED9A5EC0192B3 \
BBF19DE424084FF7951879B5EC075523BFB76DE4240A06E77F2149C5EC03EF3C2620EDF4240E8E9FB15919 \
C5EC01F435024DADF4240D991C2F6F69C5EC0B039596BD2E042403D6286AA429D5EC0E71754ADEDE14240E \
C758648719D5EC0E3F0940721E342402FDD2406819D5EC0F8C264AA60E44240EC758648719D5EC00D95344 \
DA0E542403D6286AA429D5EC0096E75A7D3E64240D991C2F6F69C5EC0404C70E9EEE74240E8E9FB15919C5 \
EC0D1427930E7E84240A06E77F2149C5EC0B29206F2B2E9424084FF7951879B5EC07B338E594AEA4240079 \
359A4ED9A5EC0D75A8E95A7EA4240FCA9F1D24D9A5EC05E29CB10C7EA4240F1C08901AE995EC0D75A8E95A \
7EA42407454695414995EC07B338E594AEA424058E56BB386985EC0B29206F2B2E94240106AE78F0A985EC \
0D1427930E7E842401FC220AFA4975EC0404C70E9EEE74240BBF15CFB58975EC0096E75A7D3E642400CDE5 \
C5D2A975EC00D95344DA0E54240C976BE9F1A975EC0F8C264AA60E44240'::geometry)</div><div> \
-> Bitmap \
Index Scan on entries_created_at (cost=0.00..7969.12 rows=256497 width=0) \
(actual time=42.857..42.857 rows=270092 loops=1)</div><div> \
Index Cond: \
(taken_at > (now() - '12:00:00'::interval))</div><div> Total runtime: 258.885 \
ms</div><div><br></div></div></div></div></div></span></div> </div>
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic