Re: Subplan and index usage

"Vyacheslav Kalinin" <vka(at)mgcp(dot)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