"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
