Re: Slow Queries with OR's? - Mailing list pgsql-general

Apologies for loosing the thread.

>"Mad Hatter" <mad_hatter_@hotmail.com> writes:
>> When I run the following query:
>> SELECT attrib1 FROM table1 WHERE attrib2 IN ( '1', '3', '5', '7' );
>
>Could we see the EXPLAIN ANALYZE output for that, as well as one of
>these?
>
> SELECT attrib1 FROM table1 WHERE attrib2 = '1'
>
>ISTM you should be getting roughly equivalent plans, that is an
>indexscan for each of the constants.
>
>(The nearby comments about IN being slow apply to "foo IN (SELECT ...)"

>which this is not.)
>
>                       regards, tom lane




Tom, I acually tried to overly simplify things, I was doing IN(SELECT).


Here's the output you requested.

EXPLAIN ANALYZE
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;
 QUERY PLAN

------------------------------------------------------------------------
------------------------------------------------------------------------
---------
Index Scan Backward using news_messages_c_date_index on news_messages (
cost=0.00..171844391116.25 rows=1 width=827) (actual time=213790.91..
425350.45 rows=20 loops=1)
Index Cond: ((col_date >= '2003-01-01 00:00:00'::timestamp without
timezone) AND (col_date <= '2003-02-01 00:00:00'::timestamp without
time zone))
Filter: ((subject ~~* '%CISCO%'::text) AND (message ~~* '%router%'::
text)AND (subplan))
SubPlan
->  Materialize  (cost=892397.63..892397.63 rows=3924463 width=43)(
actual time=9851.52..14640.18 rows=3622109 loops=20)
->  Result  (cost=0.00..892397.63 rows=3924463 width=43) (actualtime=21.
60..186933.79 rows=3924463 loops=1)
One-Time Filter: $1
InitPlan
->  Result  (cost=0.00..165027.19 rows=8054019 width=44)(actual time=13.
17..13.17 rows=1 loops=1)
One-Time Filter: $0
InitPlan
->  Seq Scan on news_ng_genre_link(cost=0.00..18.84 rows=493 width=8) (
actual time=6.58..6.58 rows=1 loops=1)
Filter: ((genre_id = 1) OR (genre_id = 5))
->  Seq Scan on news_msg_id_ng_id_link(cost=0.00..165027.19 rows=
8054019 width=44) (actual time=6.56..6.56 rows=1 loops=1)
->  Seq Scan on news_messages  (cost=0.00..892397.63 rows=3924463 width=
43) (actual time=8.42..178589.46 rows=3924463 loops=1)
Total runtime: 425470.07 msec
(16 rows)

EXPLAIN ANALYZE SELECT ng_id FROM news_ng_genre_link WHERE genre_id in (
1, 5 );
QUERY PLAN
------------------------------------------------------------------------
----------------------------------------
Seq Scan on news_ng_genre_link  (cost=0.00..18.84 rows=493 width=8) (
actual
time=0.09..12.94 rows=498 loops=1)
Filter: ((genre_id = 1) OR (genre_id = 5))
Total runtime: 13.36 msec
(3 rows)