Re: Incorrect index being used
От | Jesse Long |
---|---|
Тема | Re: Incorrect index being used |
Дата | |
Msg-id | 52565C75.1010902@unknown.za.net обсуждение исходный текст |
Ответ на | Re: Incorrect index being used (Tom Lane <tgl@sss.pgh.pa.us>) |
Список | pgsql-general |
On 09/10/2013 18:06, Tom Lane wrote: > Jesse Long <jpl@unknown.za.net> writes: >> The query runs for much longer than I expect it to run for, and I think >> this is due to it using the incorrect subplan. As you can see, subplans >> 1 and 3 make use of and index, but these subplans are not used. >> Subplans and 4 are seqscan, and they are used. >> How can I get PostgreSQL to use subplan 1 and 3? > You can't, and you would not like the results if you did. > > The construct that's being described (perhaps not very intelligibly) > by this EXPLAIN output is an alternative pair of subplans. Actually > there are two such alternative pairs in this example. The indexscan > variants are subplans that would be fast if executed only once or > twice. The seqscan variants, if used, are used to load a hashtable > that is then probed for each row of the outer plan. If there are a > lot of rows to be considered in the outer plan, then it's better to > pay the price of loading the hashtable, because each hashtable probe > will be a lot cheaper than doing a fresh indexscan with the comparison > value from the current outer row. > > In this example, we can see that the outer scan that the subplans > are attached to eliminated 710851 rows by means of the subplan filters, > meaning that the subplans were probed 710851+2 times. If each of those > probes had been done with a separate indexscan, you'd likely still be > waiting for the result. Using the seqscan+hashtable was definitely the > right choice here. > > BTW, the reason it looks like this rather than just hard-wiring the > seqscan choice is a planner implementation artifact --- at the time > that the subplan plans are created, we don't know how many rows are > expected to pass through the outer plan level. So we plan it both > ways and leave the choice to be made during executor startup. > > What I'd suggest is that you see if you can't get rid of the "EXISTS() OR > EXISTS()" construction in favor of a single EXISTS clause --- I'm too lazy > to work out the details but it looks like you could do the OR in the WHERE > clause of a single EXISTS sub-select. That would allow the planner to > convert the EXISTS into a semi-join, which might work better than what > you've got. As is, you're dealing with fairly generic sub-select logic > that isn't going to be terribly well optimized. > Hi Tom, I am very grateful for your detailed reply. I have not had much time to pursue this issue further, but as soon as I have I will investigate and study what you have written. Thanks for taking the time to write your thoughts in detail. Cheers, Jesse
В списке pgsql-general по дате отправления: