Re: Query plan choice issue

Hi Tom,

On 14/09/2010, at 12:41 AM, Tom Lane wrote:

> 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.

Well, that substring() and ILIKE combo looked suspicious to me, too.
However, there already was an index on substring(v.headervalue, 0,
255) but the fast query plan didn't seem to use it, it used a
different index instead:

mail=# \d dbmail_headervalue
Table "public.dbmail_headervalue"
Column | Type | Modifiers
----------------+--------
+-------------------------------------------------------------------
headername_id | bigint | not null
physmessage_id | bigint | not null
id | bigint | not null default
nextval('dbmail_headervalue_idnr_seq'::regclass)
headervalue | text | not null default ''::text
Indexes:
"dbmail_headervalue_pkey" PRIMARY KEY, btree (id)
"dbmail_headervalue_1" UNIQUE, btree (physmessage_id, id)
"dbmail_headervalue_2" btree (physmessage_id)
"dbmail_headervalue_3" btree ("substring"(headervalue, 0, 255))
...
EXPLAIN ANALYZE...
-> Index Scan using dbmail_headervalue_2 on
dbmail_headervalue v (cost=0.00..1489.96 rows=1 width=16) (actual
time=0.028..0.029 rows=0 loops=358)
Index Cond: (v.physmessage_id =
m.physmessage_id)
Filter: ("substring"(v.headervalue, 0, 255)
~~* '%<(dot)(dot)(dot)(at)mail(dot)gmail(dot)com>%'::text)
...

Meanwhile, a mate of mine lurking on this list pointed out that
reducing random_page_cost might help here and it did: random_page_cost
of 2 made the fast query favourable.

Can it mean that the default planner configuration slightly
overfavours seq scans?

Thank you all guys!

Yar