Re: Bitmap table scan cost per page formula

Moin, On Wed, December 20, 2017 11:51 pm, Jeff Janes wrote: > On Wed, Dec 20, 2017 at 2:18 PM, Robert Haas > wrote: > >> On Wed, Dec 20, 2017 at 4:20 PM, Jeff Janes >> wrote: >>> >>> It is not obvious to me that the parabola is wrong. I've certainly

Re: Bitmap table scan cost per page formula

Hi Jeff, The issue happens on our customer's production environment, I don't have access to their hardware. But I agree, the default value 100 is indeed a poor value. After I change the default value to 30 or less, the query starts generating plan with bitmap scan as expected. ~ ~ ~ Haisheng

Re: Bitmap table scan cost per page formula

On Wed, Dec 20, 2017 at 5:03 PM, Tom Lane wrote: > > The parabola is probably wrong in detail --- its behavior as we approach > reading all of the pages ought to be more asymptotic, seems like. > I suppose that the reason it appears to go below the seqscan cost at the > right

Re: Bitmap table scan cost per page formula

On Tue, Dec 19, 2017 at 7:25 PM, Justin Pryzby wrote: > > I started playing with this weeks ago (probably during Vitaliy's problem > report). Is there any reason cost_bitmap_heap_scan shouldn't interpolate > based > on correlation from seq_page_cost to rand_page_cost, same

Re: Bitmap table scan cost per page formula

On Tue, Dec 19, 2017 at 11:55 AM, Haisheng Yuan wrote: > Hi hackers, > > This is Haisheng Yuan from Greenplum Database. > > We had some query in production showing that planner favors seqscan over > bitmapscan, and the execution of seqscan is 5x slower than using > bitmapscan,

Re: Bitmap table scan cost per page formula

On Wed, Dec 20, 2017 at 2:18 PM, Robert Haas wrote: > On Wed, Dec 20, 2017 at 4:20 PM, Jeff Janes wrote: >> >> It is not obvious to me that the parabola is wrong. I've certainly seen >> cases where reading every 2nd or 3rd block (either

Re: Bitmap table scan cost per page formula

Robert Haas writes: > On Wed, Dec 20, 2017 at 4:20 PM, Jeff Janes wrote: >> It is not obvious to me that the parabola is wrong. I've certainly seen >> cases where reading every 2nd or 3rd block (either stochastically, or >> modulus) actually does

Re: Bitmap table scan cost per page formula

On Wed, Dec 20, 2017 at 4:20 PM, Jeff Janes wrote: > > It is not obvious to me that the parabola is wrong. I've certainly seen > cases where reading every 2nd or 3rd block (either stochastically, or > modulus) actually does take longer than reading every block, because it >

Re: Bitmap table scan cost per page formula

On Wed, Dec 20, 2017 at 12:29 PM, Robert Haas wrote: > On Tue, Dec 19, 2017 at 2:55 PM, Haisheng Yuan wrote: >> >> Below is the graph (credit to Heikki) that plots the total estimated cost >> of a bitmap heap scan, where table size is 1 pages, and >>

Re: Bitmap table scan cost per page formula

On Tue, Dec 19, 2017 at 2:55 PM, Haisheng Yuan wrote: > > Below is the graph (credit to Heikki) that plots the total estimated cost > of a bitmap heap scan, where table size is 1 pages, and > random_page_cost=10 and seq_page_cost=1. X axis is the number of pages > fetche.

Re: Bitmap table scan cost per page formula

On Tue, Dec 19, 2017 at 10:25 PM, Justin Pryzby wrote: > In this old thread: > https://www.postgresql.org/message-id/CAGTBQpZ%2BauG%2BKhcLghvTecm4-cGGgL8vZb5uA3%3D47K7kf9RgJw%40mail.gmail.com > ..Claudio Freire wrote: >> Correct me if I'm

Re: Bitmap table scan cost per page formula

On Tue, Dec 19, 2017 at 07:55:32PM +, Haisheng Yuan wrote: > Hi hackers, > > This is Haisheng Yuan from Greenplum Database. > > We had some query in production showing that planner favors seqscan over > bitmapscan, and the execution of seqscan is 5x slower than using > bitmapscan, but the

Bitmap table scan cost per page formula

Hi hackers, This is Haisheng Yuan from Greenplum Database. We had some query in production showing that planner favors seqscan over bitmapscan, and the execution of seqscan is 5x slower than using bitmapscan, but the cost of bitmapscan is 2x the cost of seqscan. The statistics were updated and