Avoiding sequential scans with OR join condition

Поиск
Список
Период
Сортировка
От Mike Mascari
Тема Avoiding sequential scans with OR join condition
Дата
Msg-id 4170B03D.3060906@mascari.com
обсуждение исходный текст
Ответы Re: Avoiding sequential scans with OR join condition  (Janning Vygen <vygen@gmx.de>)
Re: Avoiding sequential scans with OR join condition  (Martijn van Oosterhout <kleptog@svana.org>)
Re: Avoiding sequential scans with OR join condition  (Tom Lane <tgl@sss.pgh.pa.us>)
Re: Avoiding sequential scans with OR join condition  (Sim Zacks <sim@compulab.co.il>)
Список pgsql-general
Hello. I have a query like:

SELECT big_table.*
FROM little_table, big_table
WHERE little_table.x = 10 AND
little_table.y IN (big_table.y1, big_table.y2);

I have indexes on both big_table.y1 and big_table.y2 and on
little_table.x and little_table.y. The result is a sequential scan of
big_table. In order to prevent this, I've rewritten the query as:

SELECT big_table.*
FROM little_table, big_table
WHERE little_table.x = 10 AND
little_table.y = big_table.y1
  UNION
SELECT big_table.*
FROM little_table, big_table
WHERE little_table.x = 10 AND
little_table.y = big_table.y2

which does allow an index scan, but suffers from two separate queries
along with a unique sort, which, from the data, represents 90% of the
tuples returned by both queries.

Is there any way to write the first query such that indexes will be used?

Mike Mascari

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

Предыдущее
От: Michael Glaesemann
Дата:
Сообщение: Re: Has anyone tried Date/Darwen/Lorentzos's model for temporal data?
Следующее
От: William Yu
Дата:
Сообщение: Re: creating audit tables