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