Skip site navigation (1) Skip section navigation (2)

Re: Very specialised query

From: Віталій Тимчишин <tivv00(at)gmail(dot)com>
To: Matthew Wakeling <matthew(at)flymine(dot)org>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Very specialised query
Date: 2009-03-27 16:36:50
Message-ID: 331e40660903270936u5db00807sb1169df66443ade3@mail.gmail.com (view raw or flat)
Thread:
Lists: 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

In response to

Responses

pgsql-performance by date

Next:From: JeffDate: 2009-03-27 17:23:24
Subject: Re: I have a fusion IO drive available for testing
Previous:From: Tom LaneDate: 2009-03-27 14:30:02
Subject: Re: Very specialised query

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group