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'\\x0103000020e61000000100000021000000c976be9f1a975ec0f8c264aa60e442400cde5c5d2a975ec0e3f0940721e34240bbf15cfb58975ec0e71754adede142401fc220afa4975ec0b039596bd2e04240106ae78f0a985ec01f435024dadf424058e56bb386985ec03ef3c2620edf42407454695414995ec075523bfb76de4240f1c08901ae995ec0192b3bbf19de4240fca9f1d24d9a5ec0925cfe43fadd4240079359a4ed9a5ec0192b3bbf19de424084ff7951879b5ec075523bfb76de4240a06e77f2149c5ec03ef3c2620edf4240e8e9fb15919c5ec01f435024dadf4240d991c2f6f69c5ec0b039596bd2e042403d6286aa429d5ec0e71754adede14240ec758648719d5ec0e3f0940721e342402fdd2406819d5ec0f8c264aa60e44240ec758648719d5ec00d95344da0e542403d6286aa429d5ec0096e75a7d3e64240d991c2f6f69c5ec0404c70e9eee74240e8e9fb15919c5ec0d1427930e7e84240a06e77f2149c5ec0b29206f2b2e9424084ff7951879b5ec07b338e594aea4240079359a4ed9a5ec0d75a8e95a7ea4240fca9f1d24d9a5ec05e29cb10c7ea4240f1c08901ae995ec0d75a8e95a7ea42407454695414995ec07b338e594aea424058e56bb386985ec0b29206f2b2e94240106ae78
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 &&
'0103000020E61000000100000021000000C976BE9F1A975EC0F8C264AA60E442400CDE5C5D2A975EC0E3F0940721E34240BBF15CFB58975EC0E71754ADEDE142401FC220AFA4975EC0B039596BD2E04240106AE78F0A985EC01F435024DADF424058E56BB386985EC03EF3C2620EDF42407454695414995EC075523BFB76DE4240F1C08901AE995EC0192B3BBF19DE4240FCA9F1D24D9A5EC0925CFE43FADD4240079359A4ED9A5EC0192B3BBF19DE424084FF7951879B5EC075523BFB76DE4240A06E77F2149C5EC03EF3C2620EDF4240E8E9FB15919C5EC01F435024DADF4240D991C2F6F69C5EC0B039596BD2E042403D6286AA429D5EC0E71754ADEDE14240EC758648719D5EC0E3F0940721E342402FDD2406819D5EC0F8C264AA60E44240EC758648719D5EC00D95344DA0E542403D6286AA429D5EC0096E75A7D3E64240D991C2F6F69C5EC0404C70E9EEE74240E8E9FB15919C5EC0D1427930E7E84240A06E77F2149C5EC0B29206F2B2E9424084FF7951879B5EC07B338E594AEA4240079359A4ED9A5EC0D75A8E95A7EA4240FCA9F1D24D9A5EC05E29CB10C7EA4240F1C08901AE995EC0D75A8E95A7EA42407454695414995EC07B338E594AEA424058E56BB386985EC0B29206F2B2E94240106AE78F0A985EC0D1427930E7E842401FC2
20AFA497
5EC0404C70E9EEE74240BBF15CFB58975EC0096E75A7D3E642400CDE5C5D2A975EC00D95344DA0E54240C976BE9F1A975EC0F8C264AA60E44240'::geometry)
Filter: ((taken_at > (now() - '72:00:00'::interval)) AND _st_intersects(pnt,
'0103000020E61000000100000021000000C976BE9F1A975EC0F8C264AA60E442400CDE5C5D2A975EC0E3F0940721E34240BBF15CFB58975EC0E71754ADEDE142401FC220AFA4975EC0B039596BD2E04240106AE78F0A985EC01F435024DADF424058E56BB386985EC03EF3C2620EDF42407454695414995EC075523BFB76DE4240F1C08901AE995EC0192B3BBF19DE4240FCA9F1D24D9A5EC0925CFE43FADD4240079359A4ED9A5EC0192B3BBF19DE424084FF7951879B5EC075523BFB76DE4240A06E77F2149C5EC03EF3C2620EDF4240E8E9FB15919C5EC01F435024DADF4240D991C2F6F69C5EC0B039596BD2E042403D6286AA429D5EC0E71754ADEDE14240EC758648719D5EC0E3F0940721E342402FDD2406819D5EC0F8C264AA60E44240EC758648719D5EC00D95344DA0E542403D6286AA429D5EC0096E75A7D3E64240D991C2F6F69C5EC0404C70E9EEE74240E8E9FB15919C5EC0D1427930E7E84240A06E77F2149C5EC0B29206F2B2E9424084FF7951879B5EC07B338E594AEA4240079359A4ED9A5EC0D75A8E95A7EA4240FCA9F1D24D9A5EC05E29CB10C7EA4240F1C08901AE995EC0D75A8E95A7EA42407454695414995EC07B338E594AEA424058E56BB386985
EC0B2920
6F2B2E94240106AE78F0A985EC0D1427930E7E842401FC220AFA4975EC0404C70E9EEE74240BBF15CFB58975EC0096E75A7D3E642400CDE5C5D2A975EC00D95344DA0E54240C976BE9F1A975EC0F8C264AA60E44240'::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 &&
'0103000020E61000000100000021000000C976BE9F1A975EC0F8C264AA60E442400CDE5C5D2A975EC0E3F0940721E34240BBF15CFB58975EC0E71754ADEDE142401FC220AFA4975EC0B039596BD2E04240106AE78F0A985EC01F435024DADF424058E56BB386985EC03EF3C2620EDF42407454695414995EC075523BFB76DE4240F1C08901AE995EC0192B3BBF19DE4240FCA9F1D24D9A5EC0925CFE43FADD4240079359A4ED9A5EC0192B3BBF19DE424084FF7951879B5EC075523BFB76DE4240A06E77F2149C5EC03EF3C2620EDF4240E8E9FB15919C5EC01F435024DADF4240D991C2F6F69C5EC0B039596BD2E042403D6286AA429D5EC0E71754ADEDE14240EC758648719D5EC0E3F0940721E342402FDD2406819D5EC0F8C264AA60E44240EC758648719D5EC00D95344DA0E542403D6286AA429D5EC0096E75A7D3E64240D991C2F6F69C5EC0404C70E9EEE74240E8E9FB15919C5EC0D1427930E7E84240A06E77F2149C5EC0B29206F2B2E9424084FF7951879B5EC07B338E594AEA4240079359A4ED9A5EC0D75A8E95A7EA4240FCA9F1D24D9A5EC05E29CB10C7EA4240F1C08901AE995EC0D75A8E95A7EA42407454695414995EC07B338E594AEA424058E56BB386985EC0B29206F2B2E94240106AE78F0A985EC0D1427930E7E842401FC220
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 &&
'0103000020E61000000100000021000000C976BE9F1A975EC0F8C264AA60E442400CDE5C5D2A975EC0E3F0940721E34240BBF15CFB58975EC0E71754ADEDE142401FC220AFA4975EC0B039596BD2E04240106AE78F0A985EC01F435024DADF424058E56BB386985EC03EF3C2620EDF42407454695414995EC075523BFB76DE4240F1C08901AE995EC0192B3BBF19DE4240FCA9F1D24D9A5EC0925CFE43FADD4240079359A4ED9A5EC0192B3BBF19DE424084FF7951879B5EC075523BFB76DE4240A06E77F2149C5EC03EF3C2620EDF4240E8E9FB15919C5EC01F435024DADF4240D991C2F6F69C5EC0B039596BD2E042403D6286AA429D5EC0E71754ADEDE14240EC758648719D5EC0E3F0940721E342402FDD2406819D5EC0F8C264AA60E44240EC758648719D5EC00D95344DA0E542403D6286AA429D5EC0096E75A7D3E64240D991C2F6F69C5EC0404C70E9EEE74240E8E9FB15919C5EC0D1427930E7E84240A06E77F2149C5EC0B29206F2B2E9424084FF7951879B5EC07B338E594AEA4240079359A4ED9A5EC0D75A8E95A7EA4240FCA9F1D24D9A5EC05E29CB10C7EA4240F1C08901AE995EC0D75A8E95A7EA42407454695414995EC07B338E594AEA424058E56BB386985EC0B29206F2B2E94240106AE78F0A985EC0D1427930E7E842401FC
220AFA49
75EC0404C70E9EEE74240BBF15CFB58975EC0096E75A7D3E642400CDE5C5D2A975EC00D95344DA0E54240C976BE9F1A975EC0F8C264AA60E44240'::geometry)
AND (taken_at > (now() - '12:00:00'::interval)))
Filter: _st_intersects(pnt,
'0103000020E61000000100000021000000C976BE9F1A975EC0F8C264AA60E442400CDE5C5D2A975EC0E3F0940721E34240BBF15CFB58975EC0E71754ADEDE142401FC220AFA4975EC0B039596BD2E04240106AE78F0A985EC01F435024DADF424058E56BB386985EC03EF3C2620EDF42407454695414995EC075523BFB76DE4240F1C08901AE995EC0192B3BBF19DE4240FCA9F1D24D9A5EC0925CFE43FADD4240079359A4ED9A5EC0192B3BBF19DE424084FF7951879B5EC075523BFB76DE4240A06E77F2149C5EC03EF3C2620EDF4240E8E9FB15919C5EC01F435024DADF4240D991C2F6F69C5EC0B039596BD2E042403D6286AA429D5EC0E71754ADEDE14240EC758648719D5EC0E3F0940721E342402FDD2406819D5EC0F8C264AA60E44240EC758648719D5EC00D95344DA0E542403D6286AA429D5EC0096E75A7D3E64240D991C2F6F69C5EC0404C70E9EEE74240E8E9FB15919C5EC0D1427930E7E84240A06E77F2149C5EC0B29206F2B2E9424084FF7951879B5EC07B338E594AEA4240079359A4ED9A5EC0D75A8E95A7EA4240FCA9F1D24D9A5EC05E29CB10C7EA4240F1C08901AE995EC0D75A8E95A7EA42407454695414995EC07B338E594AEA424058E56BB386985EC0B29206F2B2E94240106AE78F0A985EC0D1427930E7E842
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 &&
'0103000020E61000000100000021000000C976BE9F1A975EC0F8C264AA60E442400CDE5C5D2A975EC0E3F0940721E34240BBF15CFB58975EC0E71754ADEDE142401FC220AFA4975EC0B039596BD2E04240106AE78F0A985EC01F435024DADF424058E56BB386985EC03EF3C2620EDF42407454695414995EC075523BFB76DE4240F1C08901AE995EC0192B3BBF19DE4240FCA9F1D24D9A5EC0925CFE43FADD4240079359A4ED9A5EC0192B3BBF19DE424084FF7951879B5EC075523BFB76DE4240A06E77F2149C5EC03EF3C2620EDF4240E8E9FB15919C5EC01F435024DADF4240D991C2F6F69C5EC0B039596BD2E042403D6286AA429D5EC0E71754ADEDE14240EC758648719D5EC0E3F0940721E342402FDD2406819D5EC0F8C264AA60E44240EC758648719D5EC00D95344DA0E542403D6286AA429D5EC0096E75A7D3E64240D991C2F6F69C5EC0404C70E9EEE74240E8E9FB15919C5EC0D1427930E7E84240A06E77F2149C5EC0B29206F2B2E9424084FF7951879B5EC07B338E594AEA4240079359A4ED9A5EC0D75A8E95A7EA4240FCA9F1D24D9A5EC05E29CB10C7EA4240F1C08901AE995EC0D75A8E95A7EA42407454695414995EC07B338E594AEA424058E56BB386985EC0B29206F2B2E94240106AE78F0A985EC0D1427930E7E842401FC220
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
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
