Re: Very specialised query

Поиск
Список
Период
Сортировка
От Віталій Тимчишин
Тема Re: Very specialised query
Дата
Msg-id 331e40660903270936u5db00807sb1169df66443ade3@mail.gmail.com
обсуждение исходный текст
Ответ на Very specialised query  (Matthew Wakeling <matthew@flymine.org>)
Ответы Re: Very specialised query  (Matthew Wakeling <matthew@flymine.org>)
Список 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 по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: Very specialised query
Следующее
От: Jeff
Дата:
Сообщение: Re: I have a fusion IO drive available for testing