Re: Very specialised query

Список
Период
Сортировка
От Matthew Wakeling
Тема Re: Very specialised query
Дата
Msg-id alpine.DEB.2.00.0903301101410.21772@aragorn.flymine.org
обсуждение исходный текст
Ответ на Re: Very specialised query  ("Marc Mamin")
Ответы Re: Very specialised query  ("Marc Mamin")
Список pgsql-performance
Дерево обсуждения
Very specialised query  (Matthew Wakeling, )
 Re: Very specialised query  ("Kevin Grittner", )
 Re: Very specialised query  (Tom Lane, )
  Re: Very specialised query  (Matthew Wakeling, )
 Re: Very specialised query  (Matthew Wakeling, )
  Re: Very specialised query  (Tom Lane, )
 Re: Very specialised query  (Віталій Тимчишин, )
  Re: Very specialised query  (Matthew Wakeling, )
   Re: Very specialised query  (Tom Lane, )
    Re: Very specialised query  (Matthew Wakeling, )
    Re: Very specialised query  (Matthew Wakeling, )
     Re: Very specialised query  (Віталій Тимчишин, )
      Re: Very specialised query  (Matthew Wakeling, )
       Re: Very specialised query  (Віталій Тимчишин, )
        Re: Very specialised query  (Matthew Wakeling, )
 Re: Very specialised query  (Dimitri Fontaine, )
  Re: Very specialised query  (Matthew Wakeling, )
 Re: Very specialised query  ("Marc Mamin", )
  Re: Very specialised query  (Matthew Wakeling, )
   Re: Very specialised query  ("Marc Mamin", )
    Re: Very specialised query  (Matthew Wakeling, )
 Re: Very specialised query  (Matthew Wakeling, )
  Re: Very specialised query  (Віталій Тимчишин, )
   Re: Very specialised query  (Matthew Wakeling, )
    Re: Very specialised query  (Matthew Wakeling, )
     Re: Very specialised query  (Matthew Wakeling, )
      Re: Very specialised query  (Craig Ringer, )
 Re: Very specialised query  ("Marc Mamin", )
  Re: Very specialised query  (Matthew Wakeling, )
On Fri, 27 Mar 2009, Marc Mamin wrote:
> if your data are mostly static and you have a few mains objects,
> maybe you can have some gain while defining conditional indexes for those plus one for the rest
> and then slicing the query:

Maybe. I thought about doing that. However, I am not convinced that would
be much of a gain, and would require major rewriting of the queries, as
you suggest.

> WHERE (l2.start BETWEEN  l1.start AND l1.end
>          OR
>          l1.start BETWEEN  l2.start AND l2.end
>          )

Yes, that's another way to calculate an overlap. However, it turns out to
not be that fast. The problem is that OR there, which causes a bitmap
index scan, as the leaf of a nested loop join, which can be rather slow.

Matthew

--
 I'd try being be a pessimist, but it probably wouldn't work anyway.

В списке pgsql-performance по дате отправления:

Предыдущее
От: Matthew Wakeling
Дата:
Сообщение: Re: Very specialised query
Следующее
От: Scott Marlowe
Дата:
Сообщение: Re: Forcing seq_scan off for large table joined with tiny table yeilds improved performance