I have a table which contains 5,00,000 records. I want to fetch all the rows which intersects with the given bounding box. My query is:

select * from data_master b 
         where b.geom && 'BOX(-74.5312518129245 40.4469465897231,-73.8281269653149 40.9798974884433)'::box2d 
        and b.class in (1,2,3,4,5)
        and b.statecode in ('LA');

I have build index on geom, class and statecode columns. Still this query is taking around 7 min and return around 24000 rows. My data_master table contains data of 5 states. I have also tried to break this table into individual state table. Where each state contains around 90000-120000 rows. Still there is no improvement in the query execution time.

I am using PostGIS 2.5.1 and PostgresSQL 11.1

My query execution plan is:

 ->Bitmap Heap Scan on data_master b  (cost=5456.55..47805.76 rows=20906 width=843) (actual time=65.239..106.499 rows=45388 loops=1)    
 ->Recheck Cond: (((statecode)::text = 'LA'::text) AND (geom && '01030000000100000005000000E49C9A0700A252C0B0F5BC8B35394440E49C9A0700A252C0F425E9476D7D4440A53D3E08007552C0F425E9476D7D4440A53D3E08007552C0B0F5BC8B35394440E49C9A0700A252C0B0F5BC8B35394440'::geometry))
 ->Filter: (class = ANY ('{1,2,3,4,5}'::integer[]))
 ->Heap Blocks: exact=19233
 ->  BitmapAnd  (cost=5456.55..5456.55 rows=20906 width=0) (actual time=59.390..59.390 rows=0 loops=1)
 ->  Bitmap Index Scan on idx_data_master_statecode  (cost=0.00..2320.56 rows=125351 width=0) (actual time=24.091..24.091 rows=123409 loops=1)
 ->Index Cond: ((statecode)::text = 'LA'::text)
 ->  Bitmap Index Scan on data_master_geom_gidx  (cost=0.00..3125.29 rows=89201 width=0) (actual time=31.081..31.081 rows=86010 loops=1)
 ->Index Cond: (geom && '01030000000100000005000000E49C9A0700A252C0B0F5BC8B35394440E49C9A0700A252C0F425E9476D7D4440A53D3E08007552C0F425E9476D7D4440A53D3E08007552C0B0F5BC8B35394440E49C9A0700A252C0B0F5BC8B35394440'::geometry)
 ->Planning Time: 0.476 ms
 ->Execution Time: 108.643 ms.
  • I guess that plain select * from data_master b where b.geom && 'BOX(-74.5312518129245 40.4469465897231,-73.8281269653149 40.9798974884433)'::box2druns much faster, is it true? – user30184 May 3 at 6:27
  • No, not much improvement. – Neha Sharma May 3 at 6:35
  • 1
    How about with less returned data select statecode from data_master b where b.geom && 'BOX(-74.5312518129245 40.4469465897231,-73.8281269653149 40.9798974884433)'::box2d? – user30184 May 3 at 6:39
  • 3
    Then is feels like you have a rather slow connection to your database and your table has lots of attribute data. Select just what you really need and think how you could speed up the connection. – user30184 May 3 at 7:21
  • 1
    try running standard maintenance (VACUUM ANALYZE <table>;). you could then try to let the planner use a hash join instead of a simple filter with ... WHERE b.class = ANY(VALUES (1), (2), ...) AND b.statecode = 'LA', effectively avoiding IN (or use a subquery in IN). how large is that table on disk (and does it fit into memory)? how many columns does it have? what kind of indexes did you use? – ThingumaBob May 3 at 10:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.