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'
Ry-
  • 218,210
  • 55
  • 464
  • 476
user2333312
  • 101
  • 1
  • 9
  • Can you share the `CREATE INDEX ..` statement? – Jim Jones Jun 06 '18 at 12:08
  • Please also share the `EXPLAIN ANALYZE ..` result. – Jim Jones Jun 06 '18 at 12:19
  • JimJones, thank you, I added the create index command and the explain analyze results to my original post. – user2333312 Jun 06 '18 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 –  Jun 06 '18 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. – Jim Jones Jun 06 '18 at 13:04
  • Maybe your client is the bottleneck, and that's where the 15 seconds are spent. That would explain why you see no performance improvement. – Laurenz Albe Jun 06 '18 at 13:30
  • The client I'm testing with is pgAdmin III. I changed the query to just select count(*) with the same criteria. With the index its 175 milliseconds, without its an entire second, so the index is working. In the future I will rely on the "explain analyze" results instead of the client. Thanks for the help! – user2333312 Jun 06 '18 at 13:40

0 Answers0