Re: Optimizer showing wrong rows in plan

From: Tom Lane
Subject: Re: Optimizer showing wrong rows in plan
Date: ,
Msg-id: 22618.1269793661@sss.pgh.pa.us
(view: Whole thread, Raw)
In response to: Re: Optimizer showing wrong rows in plan  (Tadipathri Raghu)
Responses: Re: Optimizer showing wrong rows in plan  (Jeremy Harris)
List: pgsql-performance

Tree view

Optimizer showing wrong rows in plan  (Tadipathri Raghu, )
 Re: Optimizer showing wrong rows in plan  (Szymon Guz, )
  Re: Optimizer showing wrong rows in plan  (Tadipathri Raghu, )
   Re: Optimizer showing wrong rows in plan  (Szymon Guz, )
    Re: Optimizer showing wrong rows in plan  (Tadipathri Raghu, )
     Re: Optimizer showing wrong rows in plan  (Tom Lane, )
      Re: Optimizer showing wrong rows in plan  (Jeremy Harris, )
       Re: Optimizer showing wrong rows in plan  (Tom Lane, )
        Re: Optimizer showing wrong rows in plan  (Tadipathri Raghu, )
         Re: Optimizer showing wrong rows in plan  (Matthew Wakeling, )
          Re: Optimizer showing wrong rows in plan  (raghavendra t, )
           Re: Optimizer showing wrong rows in plan  (Nikolas Everett, )
 Re: Optimizer showing wrong rows in plan  (Tadipathri Raghu, )
  Re: Optimizer showing wrong rows in plan  (Frank Heikens, )
  Re: Optimizer showing wrong rows in plan  (Gary Doades, )

Tadipathri Raghu <> writes:
> Frankly speaking its a newly created table without any operation on it as
> you have seen the example. Then how come it showing those many rows where we
> have only one in it.

Yes.  This is intentional: the size estimates for a never-yet-analyzed
table are *not* zero.  This is because people frequently create and load
up a table and then immediately query it without an explicit ANALYZE.
The quality of the plans you'd get at that point (especially for joins)
would be spectacularly bad if the default assumption were that the table
was very small.

            regards, tom lane


pgsql-performance by date:

From: Cédric Villemain
Date:
Subject: Re: Forcing index scan on query produces 16x faster
From: Greg Smith
Date:
Subject: Re: Database size growing over time and leads to performance impact