To: "Spiegelberg, Greg" <gspiegelberg@cranel.com>
cc: pgsql-performance@postgresql.org
Subject: Re: DELETE with filter on ctid 
In-reply-to: <82E74D266CB9B44390D3CCE44A781ED90B6ADD@POSTOFFICE.cranel.local> 
References: <82E74D266CB9B44390D3CCE44A781ED90B6ADD@POSTOFFICE.cranel.local>
Comments: In-reply-to "Spiegelberg, Greg" <gspiegelberg@cranel.com>
	message dated "Tue, 10 Apr 2007 09:35:14 -0400"
Date: Tue, 10 Apr 2007 12:46:28 -0400
Message-ID: <27253.1176223588@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

"Spiegelberg, Greg" <gspiegelberg@cranel.com> writes:
> Below is, I believe, everything pertinent to this problem.  First is the
> table in question, second is the problematic and original query, and
> final is the transaction that I have working today with the CTID
> implementation.

So the basic issue here is that data_id_table hasn't got a primary key
you could use as a join key?  I won't lecture you about that, but a lot
of people think it's bad practice not to have a recognizable primary key.

The slow query's problem seems to be mostly that the rowcount estimates
are horribly bad, leading to inappropriate choices of nestloop joins.
Are the statistics up-to-date?  You might try increasing the stats target
for data_id_table in particular.  A really brute-force test would be to
see what happens with that query if you just set enable_nestloop = 0.

As for the CTID query, my initial reaction that you shouldn't need an
index was wrong; looking into the code I see

 * There is currently no special support for joins involving CTID; in
 * particular nothing corresponding to best_inner_indexscan().	Since it's
 * not very useful to store TIDs of one table in another table, there
 * doesn't seem to be enough use-case to justify adding a lot of code
 * for that.

Maybe we should revisit that sometime, though I'm still not entirely
convinced by this example.

			regards, tom lane