Re: Subplan and index usage

"Vyacheslav Kalinin" <vka@mgcp.com> writes:
> [ poor estimation for ]

> select *
>   from cities
>  where  ( ficity_id in (
>                select ficity_id from cities_name_words
>                 where fsword like 'novgorod%'
>                 union
>                select ficity_id from cities_name_ru_words
>                 where fsword like 'novgorod%'
>           )
>           or lower(fsname) like 'novgorod%'
>           or lower(fsname_ru) like 'novgorod%'
>  )

When you have an IN at the top level of WHERE, it's flattened into a
kind of JOIN, and there's reasonably decent estimation of the
selectivity.  Unfortunately, when it's down inside an OR-clause like
this, all those smarts go out the window and it's just treated as a
generic subplan condition, with 0.5 estimated selectivity IIRC.
Improving that would be nice but it's not high on anyone's to-do list.

You might get better results if you combine three separate queries
with UNION.

            regards, tom lane