|From:||Andres Freund <andres(at)anarazel(dot)de>|
|To:||Adam Brusselback <adambrusselback(at)gmail(dot)com>|
|Cc:||Pg Hackers <pgsql-hackers(at)postgresql(dot)org>|
|Subject:||Re: Discussion on missing optimizations|
|Views:||Raw Message | Whole Thread | Download mbox | Resend email|
On 2017-10-06 21:33:16 -0400, Adam Brusselback wrote:
> Hopefully it's alright for me to post this here, please let me know if not.
> I ran across an article on blog.jooq.org comparing all the major RDBMS'
> with their ability to optimize away unnecessary work with queries which are
> less than optimal, and saw some discussion on hackernews and reddit, but I
> hadn't seen any discussion here.
> The article in question is here:
> Commercial databases seem to have a serious leg up in this area, and there
> are even some that MySQL has that Postgres doesn't.
> I was wondering which of these are planned, which have had discussion
> before and decided not to support, and which just haven't been thought of?
Hm, going through the ones we don't [fully] support:
3. JOIN Elimination
There's been a lot of discussion and several patches. There's a bunch of
problems here, one being that there's cases (during trigger firing,
before the constraint checks) where foreign keys don't hold true, so we
can't quite generally make these optimization. Another concern is
whether the added plan time is actually worthwhile.
4. Removing “Silly” Predicates
(i.e stuff like column = column)
This has deemed not to be a valuable use of plan time. I'm doubtful
about that argument, but that IIRC was the concensus the last time this
6. Predicate Merging
(i.e. combining a IN (a,b,c) and a IN (c,d,f) into a IN (c), similar
I can't remember proposals about adding this, but it seems worthwhile to
consider. I think we should be able to check for this without a lot of
7. Provably Empty Sets
8. CHECK Constraints
I think some of this should actually work with constraint exclusion
9. Unneeded Self JOIN
Can't remember discussions of this.
|Next Message||Tom Lane||2017-10-07 02:19:54||Re: Discussion on missing optimizations|
|Previous Message||Adam Brusselback||2017-10-07 01:33:16||Discussion on missing optimizations|