Postgis GIST index - no performance increase

345 views Asked by At

I have a Postgres 9.5 database with Postgis installed. I created a materialized view that is made from about 200 tables. The entire view contains 3,447,885 records. Each record has a location field of type geometry. When I query for all of the records where the location is ST_Within a bounding box, it takes 15.1 seconds to return 126,630 rows.

I built a GIST index on the field and run the query and it still takes 15.1 to return the 126,630 rows.

Example query:

SELECT ST_AsText (location) 
FROM myview 
WHERE ST_Within (location, ST_Geometry (....

Should I be seeing a significant performance increase on a materialized view of that size? Is there anything I can look for to indicate the source of performance problems?

Create index:

create index myview_location_gist on myview using gist (location);

Explain analyze:

'Bitmap Heap Scan on myview  (cost=130.56..11653.11 rows=1149 width=40) (actual time=18.377..564.760 rows=126330 loops=1)'
'  Recheck Cond: ('0103000020110F00000100000005000000D151D81DE90461C1831E458318835241D151D81DE90461C1D7A37045F81B53415C8FC23C79B860C1D7A37045F81B53415C8FC23C79B860C1831E458318835241D151D81DE90461C1831E458318835241'::geometry ~ location)'
'  Filter: _st_contains('0103000020110F00000100000005000000D151D81DE90461C1831E458318835241D151D81DE90461C1D7A37045F81B53415C8FC23C79B860C1D7A37045F81B53415C8FC23C79B860C1831E458318835241D151D81DE90461C1831E458318835241'::geometry, location)'
'  Rows Removed by Filter: 3'
'  Heap Blocks: exact=17361'
'  ->  Bitmap Index Scan on myview_location_gist  (cost=0.00..130.28 rows=3448 width=0) (actual time=15.923..15.923 rows=126333 loops=1)'
'        Index Cond: ('0103000020110F00000100000005000000D151D81DE90461C1831E458318835241D151D81DE90461C1D7A37045F81B53415C8FC23C79B860C1D7A37045F81B53415C8FC23C79B860C1831E458318835241D151D81DE90461C1831E458318835241'::geometry ~ location)'
'Planning time: 0.080 ms'
'Execution time: 570.068 ms'
0

There are 0 answers