"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