Re: Slow delete when many foreign tables are defined

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.

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

One thing that could speed up the lookups a little is to reverse your
primary keys on the child tables. Since type is a constant in them,
there's not really any point in searching for that first each time a
value needs to be looked up. Especially since in the child tables that
value has an incredibly bad selectivity: all rows have that value.

It's possible that it throws off the query planner.

That won't prevent those child tables from being checked against, but
it might alleviate the pain a bit.

--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.