Re: Query plan choice issue

Yaroslav Tykhiy <yar(at)barnet(dot)com(dot)au> writes:
> -> Bitmap Heap Scan on dbmail_headervalue v
> (cost=1409.82..221813.70 rows=2805 width=16) (actual
> time=28543.411..28623.623 rows=1 loops=1)
> Recheck Cond: (v.headername_id = n.id)
> Filter: ("substring"(v.headervalue, 0,
> 255) ~~* '%<(dot)(dot)(dot)(at)mail(dot)gmail(dot)com>%'::text)
> -> Bitmap Index Scan on
> dbmail_headervalue_testing (cost=0.00..1409.82 rows=75940 width=0)
> (actual time=17555.572..17555.572 rows=1877009 loops=1)
> Index Cond: (v.headername_id = n.id)

I think the major problem you're having is that the planner is
completely clueless about the selectivity of the condition
"substring"(v.headervalue, 0, 255) ~~* '%<(dot)(dot)(dot)(at)mail(dot)gmail(dot)com>%'
If it knew that that would match only one row, instead of several
thousand, it would likely pick a different plan.

In recent versions of PG you could probably make a noticeable
improvement in this if you just dropped the substring() restriction
... do you actually need that? Alternatively, if you don't want to
change the query logic at all, I'd try making an index on
substring(v.headervalue, 0, 255). I'm not expecting the query
to actually *use* the index, mind you. But its existence will prompt
ANALYZE to collect stats on the expression's value, and that will
help the planner with estimating the ~~* condition.

regards, tom lane