Re: [HACKERS] convert EXSITS to inner join gotcha and bug

David Rowley writes: > On 29 April 2017 at 15:39, Tom Lane wrote: >> I'm kind of strongly tempted to apply the second patch; but it would >> be fair to complain that reduce_unique_semijoins() is new development >> and should wait for v11.

Re: [HACKERS] convert EXSITS to inner join gotcha and bug

On 29 April 2017 at 15:39, Tom Lane wrote: > I'm kind of strongly tempted to apply the second patch; but it would > be fair to complain that reduce_unique_semijoins() is new development > and should wait for v11. Opinions? My vote is for the non-minimal patch. Of course, I'd

Re: [HACKERS] convert EXSITS to inner join gotcha and bug

Really, the way to fix Teodor's complaint is to recognize that the semijoin inner rel is effectively unique against the whole outer rel, and then strength-reduce the semijoin to a plain join. The infrastructure we built for unique joins is capable of proving that, we just weren't applying it in

Re: [HACKERS] convert EXSITS to inner join gotcha and bug

I wrote: > David Rowley writes: >> (On 29 April 2017 at 02:26, Tom Lane wrote: >> Seems related to the unconditional setting of extra.inner_unique to >> true for JOIN_UNIQUE_INNER jointypes in add_paths_to_joinrel() >> Setting this based on the

Re: [HACKERS] convert EXSITS to inner join gotcha and bug

David Rowley writes: > (On 29 April 2017 at 02:26, Tom Lane wrote: >> It looks like in the case that's giving wrong answers, the mergejoin >> is wrongly getting marked as "Inner Unique". Something's a bit too >> cheesy about that planner logic

Re: [HACKERS] convert EXSITS to inner join gotcha and bug

On Fri, Apr 28, 2017 at 6:59 PM, Tom Lane wrote: > David Rowley writes: > > Did you mean to attach this? > > See the link in Teodor's original message (it's actually a .bz2 file > not a .gz) > Yes, I didn't mean Teodor has renamed it. --

Re: [HACKERS] convert EXSITS to inner join gotcha and bug

(On 29 April 2017 at 02:26, Tom Lane wrote: > Alexander Korotkov writes: >> I've reproduced this bug on d981074c. >> On default config, after loading example.sql.bz2 and VACUUM ANALYZE, query >> result is OK. >> But with seqscan and hashjoin

Re: [HACKERS] convert EXSITS to inner join gotcha and bug

David Rowley writes: > Did you mean to attach this? See the link in Teodor's original message (it's actually a .bz2 file not a .gz) regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to

Re: [HACKERS] convert EXSITS to inner join gotcha and bug

On 29 April 2017 at 00:45, Alexander Korotkov wrote: > On default config, after loading example.sql.bz2 and VACUUM ANALYZE, query > result is OK. Hi, Did you mean to attach this? -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development,

Re: [HACKERS] convert EXSITS to inner join gotcha and bug

Ah, thanks for the clue about enable_hashjoin, because it wasn't reproducing for me as stated. I missed tweaked config, sorry -- Teodor Sigaev E-mail: teo...@sigaev.ru WWW: http://www.sigaev.ru/ -- Sent via pgsql-hackers mailing

Re: [HACKERS] convert EXSITS to inner join gotcha and bug

Alexander Korotkov writes: > I've reproduced this bug on d981074c. > On default config, after loading example.sql.bz2 and VACUUM ANALYZE, query > result is OK. > But with seqscan and hashjoin disabled, query returns 0 rows. Ah, thanks for the clue about

Re: [HACKERS] convert EXSITS to inner join gotcha and bug

On Fri, Apr 28, 2017 at 12:48 PM, Teodor Sigaev wrote: > Both 9.6 and 10devel are affected to addiction of query result on seqscan >> variable. >> > Oops, I was too nervious, 9.6 is not affected to enable_seqscan setting. > But it doesn't push down condition too. I've

Re: [HACKERS] convert EXSITS to inner join gotcha and bug

Both 9.6 and 10devel are affected to addiction of query result on seqscan variable. Oops, I was too nervious, 9.6 is not affected to enable_seqscan setting. But it doesn't push down condition too. -- Teodor Sigaev E-mail: teo...@sigaev.ru

[HACKERS] convert EXSITS to inner join gotcha and bug

Hi! Seems, there two issues: 1) Sometime conditions which for a first glance could be pushed down to scan are leaved as join quals. And it could be a ~30 times performance loss. 2) Number of query result depend on enabe_seqscan variable. The query explain analyze SELECT * FROM