Re: BUG #18825: Row value equality predicates do not use indices
От | Nico Williams |
---|---|
Тема | Re: BUG #18825: Row value equality predicates do not use indices |
Дата | |
Msg-id | Z8nSXfFQmjxokFyg@ubby обсуждение исходный текст |
Ответ на | Re: BUG #18825: Row value equality predicates do not use indices (Tom Lane <tgl@sss.pgh.pa.us>) |
Список | pgsql-bugs |
On Tue, Feb 25, 2025 at 07:44:42PM -0500, Tom Lane wrote: > PG Bug reporting form <noreply@postgresql.org> writes: > > DELETE FROM foo USING (SELECT dels FROM dels) AS dels > > WHERE foo = dels; -- <--- does not use indices on either table > > [ shrug... ] Works for me given the full-row indexes, although I do > have to force enable_seqscan off or reduce random_page_cost a lot, > because otherwise the planner thinks a seqscan-and-sort is cheaper. That seems like a bug, but a separate bug. > I strongly suspect it's right, because full-row indexes are going to > be bigger than the table proper. The mere fact that a plan uses > indexes does not automatically make it better than one that doesn't. If a full row index is a b-tree (or otherwise a prefix) index then one think that skip-scanning the b-tree would be much faster than scanning the table. These are tables with two or three columns, all small, and the indices are all covering indices, therefore the indices are all "bigger than the table proper", yet decomposing the row equality predicate causes indices to be used. (Again, in this example all columns are NOT NULL.) When I change the query to decompose the row equality predicate into column equality predicates then the indices are used, and the query is fast. For the reasons I give above I suspect that a full-row b-tree index would be equally fast, if only I could get the planner to try it. > Anyway, AFAICS your gripe has nothing to do with "can the planner > use these indexes", and everything to do with its cost estimates > about the value of doing it that way. I suspect that the planner doesn't know to try to decompose row equality predicates into column equality predicates to take advantage of indices on those columns. That would be a missing feature, not a bug, though I admit that it was surprising (PG is so good that this sort of thing is surprising, what can I say). As for not using the full row index, from the evidence I have the planner is indeed making a suboptimal choice here. Surely the planner can't always make the optimal choice, but I suspect row equality predicates are rare, and that when b-tree indices on full row values exist then using those should have a slightly higher weight than not using them. Nico --
В списке pgsql-bugs по дате отправления: