Re: Slow Queries with OR's?

Mr Mat psql-mail <psql-mail(at)freeuk(dot)com> writes:
> SELECT msg_id, col_date, from_line, message FROM news_messages
> WHERE
> msg_id in (
> SELECT msg_id FROM news_messages WHERE EXISTS (
> SELECT msg_id FROM news_msg_id_ng_id_link WHERE EXISTS (
> SELECT ng_id FROM news_ng_genre_link WHERE genre_id in( 1, 5 )
> )
> )
> )
> AND col_date >= '2003-01-01'
> AND col_date <= '2003-02-01'
> AND news_messages.subject ILIKE '%CISCO%'
> AND news_messages.message ILIKE '%router%' ORDER BY col_date DESC;

I'm not sure what you intend that "msg_id in (...)" to accomplish,
but you could leave it out and get exactly the same results, so I
suspect it is not doing what you intended. The EXISTS() clause is
independent of the containing SELECT, therefore will be true (or not,
but according to the explain analyze output it was true) at every
row, therefore the result of the SELECT just below IN consists of
every msg_id in news_messages, and so the IN succeeds --- slowly
and painfully --- at every row.

regards, tom lane