Re: Benchmark shows very slow bulk delete

Kevin Grittner wrote:
> It is if you don't have an index on the table which has a foreign
> key defined which references the table in which you're doing
> deletes.  The author of the benchmark apparently didn't realize that
> MySQL automatically adds such an index to the dependent table, while
> PostgreSQL leaves it to you to decide whether to add such an index.
>

The author there didn't write the PostgreSQL schema; he's just using the
osdb test kit:  http://osdb.sourceforge.net/

Given that both Peter and Neil Conway have thrown work their way, I know
there's been some PG specific work done on that project by people who
know what's going on, but I'm not sure if that included a performance
check.  A quick glance at
http://osdb.cvs.sourceforge.net/viewvc/osdb/osdb/src/callable-sql/postgres-ui/osdb-pg-ui.m4?revision=1.4&view=markup
finds this:

  222 createIndexForeign(char* tName, char* keyName, char* keyCol,
  223                         char* fTable, char* fFields) {
  224     snprintf(cmd, CMDBUFLEN,
  225         "alter table %s add constraint %s foreign key (%s) references %s (%s)",
  226         tName, keyName, keyCol, fTable, fFields);


But I don't see any obvious spot where the matching index that should go
along with that is created at.  The code is just convoluted enough (due
to how they abstract away support for multiple databases) that I'm not
sure yet--maybe they call their createIndexBtree function and fix this
in a later step.  But the way the function is
named--"createIndexForeign"--seems to suggest they believe that this
operation will create the index, too, which as you point out is just not
true.

--
Greg Smith    2ndQuadrant   Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com  www.2ndQuadrant.com