Re: Convert check constraints into One-Time_Filter on prepared statements

ITAGAKI Takahiro <itagaki(dot)takahiro(at)oss(dot)ntt(dot)co(dot)jp> writes:

> EXPLAIN EXECUTE p('2008-07-01');
> -------------------------------------------------------------------------
> Append
> -> Result
> One-Time Filter: (('2008-01-01' <= $1) AND ($1 < '2009-01-01'))
> -> Index Scan on test_2008_t_key
> Index Cond: (($1 <= t) AND (t < ($1 + '1 mon')))
>
> We can avoid internal scans when One-Time Filter returns false for each
> partition. So we can reuse the plan and receive benefit from CE.
>
> Is this concept ok and worth trying?
> If it is reasonable, I'll try it. Comments welcome.

It's tempting to go straight for the special case where we can detect that the
constraints are mutually exclusive and we can build a lookup table or do a
binary search to find the single partition which is included.

But the this would handle the general case and would be a huge help. I've
looked at plans where partitioning represented a huge slowdown and the time
seemed to just disappear down the hole. All the time was being spent in just
the index lookup startup and probe for dozens of partitions which returned no
rows.

In combination with the ordered append node -- which is still sitting in my
experimental directory -- this would basically "fix" partitioning. The planner
would recognize that the result is ordered and merge-joinable and it wouldn't
waste any time doing any index probes.

There would be a much smaller waste of time setting up the degenerate heap
merge and checking all those conditions in the executor. These things will
matter when we get to scaling up to hundreds of plan nodes but the situation
would still be much better than today.

The other reason I think it's still worth doing this even though it's not as
effective as covering the special case of mutually exclusive partitions is
that there's still a place for this even once we have a more mature
partitioning scheme.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's On-Demand Production Tuning