[postgis-users] Improving query planner estimates for geo column?

[Mike Krieger]

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'\\x0103000020e61000000100000021000000c976be9f1a975ec0f8c264aa60e442400cde5c5d2a975ec0e3f0940721e34240bbf15cfb58975ec0e71754adede142401fc220afa4975ec0b039596bd2e04240106ae78f0a985ec01f435024dadf424058e56bb386985ec03ef3c2620edf42407454695414995ec075523bfb76de4240f1c08901ae995ec0192b3bbf19de4240fca9f1d24d9a5ec0925cfe43fadd4240079359a4ed9a5ec0192b3bbf19de424084ff7951879b5ec075523bfb76de4240a06e77f2149c5ec03ef3c2620edf4240e8e9fb15919c5ec01f435024dadf4240d991c2f6f69c5ec0b039596bd2e042403d6286aa429d5ec0e71754adede14240ec758648719d5ec0e3f0940721e342402fdd2406819d5ec0f8c264aa60e44240ec758648719d5ec00d95344da0e542403d6286aa429d5ec0096e75a7d3e64240d991c2f6f69c5ec0404c70e9eee74240e8e9fb15919c5ec0d1427930e7e84240a06e77f2149c5ec0b29206f2b2e9424084ff7951879b5ec07b338e594aea4240079359a4ed9a5ec0d75a8e95a7ea4240fca9f1d24d9a5ec05e29cb10c7ea4240f1c08901ae995ec0d75a8e95a7ea42407454695414995ec07b338e594aea424058e56bb386985ec0b29206f2b2e94240106ae78f0a985ec
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 && '0103000020E61000000100000021000000C976BE9F1A975EC0F8C264AA60E442400CDE5C5D2A975EC0E3F0940721E34240BBF15CFB58975EC0E71754ADEDE142401FC220AFA4975EC0B039596BD2E04240106AE78F0A985EC01F435024DADF424058E56BB386985EC03EF3C2620EDF42407454695414995EC075523BFB76DE4240F1C08901AE995EC0192B3BBF19DE4240FCA9F1D24D9A5EC0925CFE43FADD4240079359A4ED9A5EC0192B3BBF19DE424084FF7951879B5EC075523BFB76DE4240A06E77F2149C5EC03EF3C2620EDF4240E8E9FB15919C5EC01F435024DADF4240D991C2F6F69C5EC0B039596BD2E042403D6286AA429D5EC0E71754ADEDE14240EC758648719D5EC0E3F0940721E342402FDD2406819D5EC0F8C264AA60E44240EC758648719D5EC00D95344DA0E542403D6286AA429D5EC0096E75A7D3E64240D991C2F6F69C5EC0404C70E9EEE74240E8E9FB15919C5EC0D1427930E7E84240A06E77F2149C5EC0B29206F2B2E9424084FF7951879B5EC07B338E594AEA4240079359A4ED9A5EC0D75A8E95A7EA4240FCA9F1D24D9A5EC05E29CB10C7EA4240F1C08901AE995EC0D75A8E95A7EA42407454695414995EC07B338E594AEA424058E56BB386985EC0B29206F2B2E94240106AE78F0A985EC0D1427930E7E842401FC220AFA497
5EC0404C70E9EEE74240BBF15CFB58975EC0096E75A7D3E642400CDE5C5D2A975EC00D95344DA0E54240C976BE9F1A975EC0F8C264AA60E44240'::geometry)
 Filter: ((taken_at > (now() - '72:00:00'::interval)) AND _st_intersects(pnt, '0103000020E61000000100000021000000C976BE9F1A975EC0F8C264AA60E442400CDE5C5D2A975EC0E3F0940721E34240BBF15CFB58975EC0E71754ADEDE142401FC220AFA4975EC0B039596BD2E04240106AE78F0A985EC01F435024DADF424058E56BB386985EC03EF3C2620EDF42407454695414995EC075523BFB76DE4240F1C08901AE995EC0192B3BBF19DE4240FCA9F1D24D9A5EC0925CFE43FADD4240079359A4ED9A5EC0192B3BBF19DE424084FF7951879B5EC075523BFB76DE4240A06E77F2149C5EC03EF3C2620EDF4240E8E9FB15919C5EC01F435024DADF4240D991C2F6F69C5EC0B039596BD2E042403D6286AA429D5EC0E71754ADEDE14240EC758648719D5EC0E3F0940721E342402FDD2406819D5EC0F8C264AA60E44240EC758648719D5EC00D95344DA0E542403D6286AA429D5EC0096E75A7D3E64240D991C2F6F69C5EC0404C70E9EEE74240E8E9FB15919C5EC0D1427930E7E84240A06E77F2149C5EC0B29206F2B2E9424084FF7951879B5EC07B338E594AEA4240079359A4ED9A5EC0D75A8E95A7EA4240FCA9F1D24D9A5EC05E29CB10C7EA4240F1C08901AE995EC0D75A8E95A7EA42407454695414995EC07B338E594AEA424058E56BB386985EC0B2920
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 && '0103000020E61000000100000021000000C976BE9F1A975EC0F8C264AA60E442400CDE5C5D2A975EC0E3F0940721E34240BBF15CFB58975EC0E71754ADEDE142401FC220AFA4975EC0B039596BD2E04240106AE78F0A985EC01F435024DADF424058E56BB386985EC03EF3C2620EDF42407454695414995EC075523BFB76DE4240F1C08901AE995EC0192B3BBF19DE4240FCA9F1D24D9A5EC0925CFE43FADD4240079359A4ED9A5EC0192B3BBF19DE424084FF7951879B5EC075523BFB76DE4240A06E77F2149C5EC03EF3C2620EDF4240E8E9FB15919C5EC01F435024DADF4240D991C2F6F69C5EC0B039596BD2E042403D6286AA429D5EC0E71754ADEDE14240EC758648719D5EC0E3F0940721E342402FDD2406819D5EC0F8C264AA60E44240EC758648719D5EC00D95344DA0E542403D6286AA429D5EC0096E75A7D3E64240D991C2F6F69C5EC0404C70E9EEE74240E8E9FB15919C5EC0D1427930E7E84240A06E77F2149C5EC0B29206F2B2E9424084FF7951879B5EC07B338E594AEA4240079359A4ED9A5EC0D75A8E95A7EA4240FCA9F1D24D9A5EC05E29CB10C7EA4240F1C08901AE995EC0D75A8E95A7EA42407454695414995EC07B338E594AEA424058E56BB386985EC0B29206F2B2E94240106AE78F0A985EC0D1427930E7E842401FC220AFA4975E
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 && '0103000020E61000000100000021000000C976BE9F1A975EC0F8C264AA60E442400CDE5C5D2A975EC0E3F0940721E34240BBF15CFB58975EC0E71754ADEDE142401FC220AFA4975EC0B039596BD2E04240106AE78F0A985EC01F435024DADF424058E56BB386985EC03EF3C2620EDF42407454695414995EC075523BFB76DE4240F1C08901AE995EC0192B3BBF19DE4240FCA9F1D24D9A5EC0925CFE43FADD4240079359A4ED9A5EC0192B3BBF19DE424084FF7951879B5EC075523BFB76DE4240A06E77F2149C5EC03EF3C2620EDF4240E8E9FB15919C5EC01F435024DADF4240D991C2F6F69C5EC0B039596BD2E042403D6286AA429D5EC0E71754ADEDE14240EC758648719D5EC0E3F0940721E342402FDD2406819D5EC0F8C264AA60E44240EC758648719D5EC00D95344DA0E542403D6286AA429D5EC0096E75A7D3E64240D991C2F6F69C5EC0404C70E9EEE74240E8E9FB15919C5EC0D1427930E7E84240A06E77F2149C5EC0B29206F2B2E9424084FF7951879B5EC07B338E594AEA4240079359A4ED9A5EC0D75A8E95A7EA4240FCA9F1D24D9A5EC05E29CB10C7EA4240F1C08901AE995EC0D75A8E95A7EA42407454695414995EC07B338E594AEA424058E56BB386985EC0B29206F2B2E94240106AE78F0A985EC0D1427930E7E842401FC220AFA49
75EC0404C70E9EEE74240BBF15CFB58975EC0096E75A7D3E642400CDE5C5D2A975EC00D95344DA0E54240C976BE9F1A975EC0F8C264AA60E44240'::geometry) AND (taken_at > (now() - '12:00:00'::interval)))
 Filter: _st_intersects(pnt, '0103000020E61000000100000021000000C976BE9F1A975EC0F8C264AA60E442400CDE5C5D2A975EC0E3F0940721E34240BBF15CFB58975EC0E71754ADEDE142401FC220AFA4975EC0B039596BD2E04240106AE78F0A985EC01F435024DADF424058E56BB386985EC03EF3C2620EDF42407454695414995EC075523BFB76DE4240F1C08901AE995EC0192B3BBF19DE4240FCA9F1D24D9A5EC0925CFE43FADD4240079359A4ED9A5EC0192B3BBF19DE424084FF7951879B5EC075523BFB76DE4240A06E77F2149C5EC03EF3C2620EDF4240E8E9FB15919C5EC01F435024DADF4240D991C2F6F69C5EC0B039596BD2E042403D6286AA429D5EC0E71754ADEDE14240EC758648719D5EC0E3F0940721E342402FDD2406819D5EC0F8C264AA60E44240EC758648719D5EC00D95344DA0E542403D6286AA429D5EC0096E75A7D3E64240D991C2F6F69C5EC0404C70E9EEE74240E8E9FB15919C5EC0D1427930E7E84240A06E77F2149C5EC0B29206F2B2E9424084FF7951879B5EC07B338E594AEA4240079359A4ED9A5EC0D75A8E95A7EA4240FCA9F1D24D9A5EC05E29CB10C7EA4240F1C08901AE995EC0D75A8E95A7EA42407454695414995EC07B338E594AEA424058E56BB386985EC0B29206F2B2E94240106AE78F0A985EC0D1427930E7E842401FC220
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 && '0103000020E61000000100000021000000C976BE9F1A975EC0F8C264AA60E442400CDE5C5D2A975EC0E3F0940721E34240BBF15CFB58975EC0E71754ADEDE142401FC220AFA4975EC0B039596BD2E04240106AE78F0A985EC01F435024DADF424058E56BB386985EC03EF3C2620EDF42407454695414995EC075523BFB76DE4240F1C08901AE995EC0192B3BBF19DE4240FCA9F1D24D9A5EC0925CFE43FADD4240079359A4ED9A5EC0192B3BBF19DE424084FF7951879B5EC075523BFB76DE4240A06E77F2149C5EC03EF3C2620EDF4240E8E9FB15919C5EC01F435024DADF4240D991C2F6F69C5EC0B039596BD2E042403D6286AA429D5EC0E71754ADEDE14240EC758648719D5EC0E3F0940721E342402FDD2406819D5EC0F8C264AA60E44240EC758648719D5EC00D95344DA0E542403D6286AA429D5EC0096E75A7D3E64240D991C2F6F69C5EC0404C70E9EEE74240E8E9FB15919C5EC0D1427930E7E84240A06E77F2149C5EC0B29206F2B2E9424084FF7951879B5EC07B338E594AEA4240079359A4ED9A5EC0D75A8E95A7EA4240FCA9F1D24D9A5EC05E29CB10C7EA4240F1C08901AE995EC0D75A8E95A7EA42407454695414995EC07B338E594AEA424058E56BB386985EC0B29206F2B2E94240106AE78F0A985EC0D1427930E7E842401FC220AFA4975E
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

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20110419/4bd3b3b0/attachment.html>