Re: Slow delete when many foreign tables are defined

On 12/1/2014 10:37 AM, Alban Hertroys wrote:
> On 1 December 2014 at 17:21, Giuseppe Sacco
> <giuseppe(at)eppesuigoccas(dot)homedns(dot)org> wrote:
>> Il giorno lun, 01/12/2014 alle 09.49 -0600, Andy Colson ha scritto:
>>> On 12/1/2014 9:23 AM, Giuseppe Sacco wrote:
>
>>> 2) Try inheritance. I have no idea if it'll help, but I thought I'd
>>> read someplace where the planner knew a little more about what types of
>>> rows go into which tables.
>
> Andy is referring to a feature called "constraint exclusion". I'm not
> sure why that doesn't kick in with your table definition though.
>
> If you get that working with your schema, your problem should be
> solved. It's possible that it only works correctly with table
> inheritance though.
>

Yep, that's what I was thinking, and seeing this:

http://www.postgresql.org/docs/9.1/static/ddl-partitioning.html

Which says:
"6. Ensure that the constraint_exclusion configuration parameter is not
disabled in postgresql.conf. If it is, queries will not be optimized as
desired."

leads me to believe it only works with table inheritance.

> This would probably help, but we are blocked on ANSI SQL for easily
> porting our application to other DBMSes.

There is very little difference in syntax. You'd always create many
detail tables except in PG you'd need tiny different syntax. If it
worked, it might be worth it. Maybe?

-Andy