0

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'
Improve this question
7
  • Can you share the CREATE INDEX .. statement? Commented Jun 6, 2018 at 12:08
  • Please also share the EXPLAIN ANALYZE .. result. Commented Jun 6, 2018 at 12:19
  • JimJones, thank you, I added the create index command and the explain analyze results to my original post. Commented Jun 6, 2018 at 12:56
  • 3
    The query only takes 570 milliseconds - that's about half a second. Not 15 seconds. Most probably your SQL client (or application) needs 15 seconds to process and display 126330 rows Commented Jun 6, 2018 at 12:58
  • 1
    @user2333312 I can't see any problem with your strategy. The index is being used, and 126330 rows in ~570ms is imho pretty fast. Commented Jun 6, 2018 at 13:04
Related questions
2
Spatial gist index in postgis - performance
1
PostgreSQL point_ops with GiST PostGIS Spatial Index
11
Postgres hstore: GIN vs GiST index performance
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.