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'