"Spiegelberg, Greg" <gspiegelberg@xxxxxxxxxx> 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
