| From: | Andres Freund <andres(at)anarazel(dot)de> |
|---|---|
| To: | Andrei Lepikhov <lepihov(at)gmail(dot)com> |
| Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Илья Жарков <izharkov1243(at)gmail(dot)com>, pgsql-hackers(at)lists(dot)postgresql(dot)org, p(dot)petrov(at)postgrespro(dot)ru |
| Subject: | Re: Do not scan index in right table if condition for left join evaluates to false using columns in left table |
| Date: | 2024-12-08 02:52:20 |
| Message-ID: | prmcdabei4jl24v7ubbd6rdwjwh4e2c3zgycq6nyxt3m5zo3qx@mz2i6l6r4jdv |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
Hi,
On 2024-12-08 09:23:42 +0700, Andrei Lepikhov wrote:
> To determine how difficult it could be, I wrote a prototype (very raw),
> which shows how it works in action and how many subsystems we have to touch.
Cool!
> Also, I wonder why you think it could work with NestLoop only.
It doesn't seem to apply to mergejoins. For hashjoins, it doesn't seem worth a
separate evaluation of a expression - something which has noticeable overhead
on its own - given that the cost of a lookup in the hashtable isn't
particularly high. Compare that to the nestloop case where you always need an
indexscan and often will have more complicated subtrees being executed
unnecessarily on the inner side.
> I think avoiding touching a hash table and an index under MergeJoin can also
> be beneficial.
How would you get significant wins for mergejoins? You need to go through both
inner and outer anyway?
Greetings,
Andres Freund
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Tom Lane | 2024-12-08 03:11:22 | Re: Do not scan index in right table if condition for left join evaluates to false using columns in left table |
| Previous Message | Andrei Lepikhov | 2024-12-08 02:23:42 | Re: Do not scan index in right table if condition for left join evaluates to false using columns in left table |