Re: [BUGS] BUG #14646: performance hint to remove
| От | David G. Johnston |
|---|---|
| Тема | Re: [BUGS] BUG #14646: performance hint to remove |
| Дата | |
| Msg-id | CAKFQuwbXL2EjTkQvUrapt8rEv1h93MskWj6FAUwD4mUjj7E58A@mail.gmail.com обсуждение исходный текст |
| Ответ на | [BUGS] BUG #14646: performance hint to remove (boshomi@gmail.com) |
| Ответы |
Re: [BUGS] BUG #14646: performance hint to remove
|
| Список | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 14646
Logged by: Boshomi
Email address: boshomi@gmail.com
PostgreSQL version: 9.6.2
Operating system: Linux opensuse
Description:
the order of tables in from clause or where clause can result in different
query plans, with different execution time.
[...]
--fast query big table left
explain analyze select * from testjoinperf.tempbig join
testjoinperf.tempsmall using(idsmall)
where idsmall between 48000 and 49000
-- slow query, small table left.
explain analyze select * from testjoinperf.tempsmall join
testjoinperf.tempbig using(idsmall)
where idsmall between 48000 and 49000
Confirmed 9.6.2 on Ubuntu. Not sure this is properly classified as a bug but its definitely an area where improvement would seem desirable. I am a particularly heavy user of the join predicate "USING" and never really thought to look at this dynamic (without the WHERE clause it doesn't matter, both tables up end sequentially scanned).
Someone more knowledgeable than myself will need to comment on the technical aspects as to why "where idsmall" seems to be linked to the left relation instead of the one with a more favorable execution plan.
Boshomi, how did you stumble across this anyway - just with artificial data or did you come up with that after hitting the problem with real data?
David J.
В списке pgsql-bugs по дате отправления: