From: Tom Lane <tgl@sss.pgh.pa.us>
To: Spiegelberg, Greg <gspiegelberg@cranel.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: DELETE with filter on ctid
Date: Tue, 10 Apr 2007 12:46:28 -0400
Message-ID: <27253.1176223588@sss.pgh.pa.us> (raw)
In-Reply-To: <82E74D266CB9B44390D3CCE44A781ED90B6ADD@POSTOFFICE.cranel.local>
References: <82E74D266CB9B44390D3CCE44A781ED90B6ADD@POSTOFFICE.cranel.local>
"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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, gspiegelberg@cranel.com
Subject: Re: DELETE with filter on ctid
In-Reply-To: <27253.1176223588@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox