Re: [HACKERS] Experimental evaluation of PostgreSQL's query optimizer

On 24 December 2015 at 03:43, Jim Nasby wrote: > On 12/21/15 7:49 PM, Craig Ringer wrote: > >> CREATE JOIN STATISTICS ON t1 JOIN t2 USING (somecol); >> >> >> That way we let an admin who's tuning queries direct effort at problem >> areas. It's not automagical, but it's an area where tools could a

Re: [HACKERS] Experimental evaluation of PostgreSQL's query optimizer

On 12/21/15 8:36 AM, Tom Lane wrote: but it might be nice to have some number as to how > reliable a certain estimate is, which is high if the estimate is, say, > derived from a single filter on a base table and sinks as more conditions > are involved or numbers pulled out of thin air.

Re: [HACKERS] Experimental evaluation of PostgreSQL's query optimizer

On 12/21/15 7:49 PM, Craig Ringer wrote: CREATE JOIN STATISTICS ON t1 JOIN t2 USING (somecol); That way we let an admin who's tuning queries direct effort at problem areas. It's not automagical, but it's an area where tools could analyze pg_stat_statements to direct effort, much like is current

Re: [HACKERS] Experimental evaluation of PostgreSQL's query optimizer

Robert Haas writes: > From my point of view, one interesting fact about database > optimization is that the numbers 0 and 1 are phenomenally important > special cases. Yeah. > It is often the case that a join will return at most 1 > row per outer row, or that an aggregate will generate exactly 1

Re: [HACKERS] Experimental evaluation of PostgreSQL's query optimizer

On Tue, Dec 22, 2015 at 3:28 AM, Viktor Leis wrote: > I get 128.894ms vs. 183.724ms, i.e., a 43% slowdown for the hash > join. However, let me stress that this is really the extreme case: > > - If the join has few matches (due to inserting a value different from > 1 into r2), hash and nested loop

Re: [HACKERS] Experimental evaluation of PostgreSQL's query optimizer

2015-12-22 9:28 GMT+01:00 Viktor Leis : > On 12/22/2015 02:40 AM, Craig Ringer wrote: > > On 21 December 2015 at 23:57, Viktor Leis l...@in.tum.de>> wrote: > > > > > > > > Please have a look at Figure 6 (page 6) in > > http://www.vldb.org/pvldb/vol9/p204-leis.pdf Disabling nested loop > >

Re: [HACKERS] Experimental evaluation of PostgreSQL's query optimizer

On 12/22/2015 02:40 AM, Craig Ringer wrote: > On 21 December 2015 at 23:57, Viktor Leis > wrote: > > > > Please have a look at Figure 6 (page 6) in > http://www.vldb.org/pvldb/vol9/p204-leis.pdf Disabling nested loop > joins without index scan (going from (a)

Re: [HACKERS] Experimental evaluation of PostgreSQL's query optimizer

On 21 December 2015 at 20:53, Viktor Leis wrote: > I think your suggestion amounts to caching the cardinalities of all > two-way joins. One major issue is that for a query like > > select * from r1, r2 where r1.x = r2.y and r1.a = ? and r2.b; > > it depends on the specific values of r1.a and r2.

Re: [HACKERS] Experimental evaluation of PostgreSQL's query optimizer

On 21 December 2015 at 23:57, Viktor Leis wrote: > > Please have a look at Figure 6 (page 6) in > http://www.vldb.org/pvldb/vol9/p204-leis.pdf Disabling nested loop > joins without index scan (going from (a) to (b)) results in great > improvements across the board. And even more importantly, it

Re: [HACKERS] Experimental evaluation of PostgreSQL's query optimizer

Am 21.12.2015 um 15:42 schrieb Tom Lane: > Viktor Leis writes: >> I think it would be a good start to distinguish between nested loop >> joins with and without a index. > > We do. > >> In my opinion, the latter should simply NEVER be chosen. > > So, if you're given a query with a non-equality j

Re: [HACKERS] Experimental evaluation of PostgreSQL's query optimizer

Viktor Leis writes: > I think it would be a good start to distinguish between nested loop > joins with and without a index. We do. > In my opinion, the latter should simply NEVER be chosen. So, if you're given a query with a non-equality join condition that doesn't match any index on either tab

Re: [HACKERS] Experimental evaluation of PostgreSQL's query optimizer

Albe Laurenz writes: > - I also can corroborate your finding that nested loop joins are often > harmful, particularly when the inner loop is a sequential scan. > One of the first things I do when investigating bad performance of a query > whose plan has a nestend loop join is to set enable_n

Re: [HACKERS] Experimental evaluation of PostgreSQL's query optimizer

Am 21.12.2015 um 09:22 schrieb Albe Laurenz: > Viktor Leis wrote: >> We have recently performed an experimental evaluation of PostgreSQL's >> query optimizer. For example, we measured the contributions of >> cardinality estimation and the cost model on the overall query >> performance. You can down

Re: [HACKERS] Experimental evaluation of PostgreSQL's query optimizer

Viktor Leis wrote: > We have recently performed an experimental evaluation of PostgreSQL's > query optimizer. For example, we measured the contributions of > cardinality estimation and the cost model on the overall query > performance. You can download the resulting paper here: > http://www.vldb.or

Re: [HACKERS] Experimental evaluation of PostgreSQL's query optimizer

On 16 December 2015 at 09:51, Viktor Leis wrote: > Hi, > > We have recently performed an experimental evaluation of PostgreSQL's > query optimizer. For example, we measured the contributions of > cardinality estimation and the cost model on the overall query > performance. You can download the re

[HACKERS] Experimental evaluation of PostgreSQL's query optimizer

Hi, We have recently performed an experimental evaluation of PostgreSQL's query optimizer. For example, we measured the contributions of cardinality estimation and the cost model on the overall query performance. You can download the resulting paper here: http://www.vldb.org/pvldb/vol9/p204-leis.p