Re: Very specialised query - Mailing list pgsql-performance

From Віталій Тимчишин
Subject Re: Very specialised query
Date
Msg-id 331e40660903270936u5db00807sb1169df66443ade3@mail.gmail.com
Whole thread Raw
In response to Very specialised query  (Matthew Wakeling <matthew@flymine.org>)
Responses Re: Very specialised query  (Matthew Wakeling <matthew@flymine.org>)
List pgsql-performance
Hello.

You could try  adding    "AND l2.start > l1.start" to the first query.  This will drop symmetric half of intersections (the ones that will remain are l2 inside or to the left of l1), but you can redo results by
id1,id2 union all id2, id1 and may allow to use start index for "between",  for my "like" test this looks like the next:

"  ->  Index Scan using location__start on location l2  (cost=0.00..756.34 rows=37787 width=12)"
"        Index Cond: ((l2.start < l1.eend) AND (l2.start > l1.start))"

also an index on (objectid, start) would help resulting in :

"  ->  Index Scan using lt on location l2  (cost=0.00..0.84 rows=20 width=16)"
"        Index Cond: ((l2.objectid = l1.objectid) AND (l2.start < l1.eend) AND (l2.start > l1.start))"

Best regards,
 Vitalii Tymchyshyn

pgsql-performance by date:

Previous
From: Tom Lane
Date:
Subject: Re: Very specialised query
Next
From: Jeff
Date:
Subject: Re: I have a fusion IO drive available for testing