Re: Very specialised query

Список
Период
Сортировка
От Marc Mamin
Тема Re: Very specialised query
Дата
Msg-id C4DAC901169B624F933534A26ED7DF311F9C05@JENMAIL01.ad.intershop.net
обсуждение исходный текст
Ответ на Very specialised query  (Matthew Wakeling)
Ответы Re: Very specialised query  (Matthew Wakeling)
Список 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, )

Hello,

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:


create index o_1x on X (start,end,id) where object_id = 1
create index o_2x on X (start,end,id) where object_id = 2
create index o_3x on X (start,end,id) where object_id = 3
create index o_4x on X (start,end,id) where object_id = 4
...
create index o_4x on X (start,end,id) where object_id not in (1,2,3,4..)


I'm not sure that putting all in one index and using the BETWEEN clause
as in my example is the best method though.

Marc Mamin


SELECT
    l1.id AS id1,
    l2.id AS id2
FROM
    location l1,
    location l2
WHERE l1.objectid = 1
    AND (l2.start BETWEEN  l1.start AND l1.end
         OR
         l1.start BETWEEN  l2.start AND l2.end
         )
         l1.start
    AND l2.start <> l2.start -- if required
    AND l2.start <> l2.end   -- if required
    AND l1.id <> l2.id


UNION ALL

...
        WHERE l1.objectid = 2
...    

UNION ALL

...
        WHERE l1.objectid not in (1,2,3,4..)

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

Предыдущее
От: "Marc Mamin"
Дата:
Сообщение: Re: Very specialised query
Следующее
От: Josh Berkus
Дата:
Сообщение: Re: Proposal of tunable fix for scalability of 8.4