Re: [PERFORM] Talking about optimizer, my long dream

[Frank Heikens]

On 04 Feb, 2011,at 02:56 PM, Mladen Gogala mladen.gog...@vmsinfo.com wrote:


Віталій Тимчишин wrote:
 Hi, all.

 All this optimizer vs hint thread
There is no optimizer vs. hint. Hints are a necessary part of the
optimizer in all other databases. 


That has nothing to do with PostgreSQL: PostgreSQL = PostgreSQL. And it doesn't 
have hints and everybody knows it.

Without hints Postgres will not get
used in the company that I work for, period. 


That's up to you, that's fine. But why did you start with PostgreSQL in the 
first place? You knew PostgreSQL doesn't have hints and the wiki told you hints 
are not wanted as well. When hints are an essential requirement for your 
company, you should pick another product, EnterpriseDB Postgres Plus for 
example.

I was willing to wait but
the fatwa against hints seems unyielding, 


There is no fatwa. The PostgreSQL project prefers to spend resources on a 
better optimizer to solve the real problems, not on hints for working around 
the problems. That has nothing to do with any fatwa or religion.

so that's it. I am even
inclined to believe that deep down under the hood, this fatwa has an
ulterior motive, which disgusts me deeply. With hints, there would be
far fewer consulting gigs.


The consulting guys are the ones who love hints: They know they have to come 
back the other month because the old hint does more harm than good when data 
changes. And data will change over time.

You said it's so simple to implement hints in PostgreSQL, so please, show us. 
Or ask/pay somebody to write this simple code for you to support hints, nobody 
will ever stop you from doing that. When you have a use case that proves the 
usage of hints will improve the performance of PostgreSQL and you have some 
code that can be maintained by the PostgreSQL project, it might be implemented 
in the contrib or even core. It's up to you, not somebody else.



Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
www.vmsinfo.com


--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Optimizer showing wrong rows in plan

[Frank Heikens]

Op 28 mrt 2010, om 11:07 heeft Tadipathri Raghu het volgende geschreven:


Hi All,

I want to give some more light on this by analysing more like this

1. In my example I have created a table with one column as  
INT( which occupies 4 bytes)

2. Initially it occupies one page of  space on the file that is (8kb).

So, here is it assuming these many rows may fit in this page.  
Clarify me on this Please.


See these chapters in the manual: 
http://www.postgresql.org/docs/8.4/interactive/storage.html

The minimum size of a file depends on the block size, by default 8kb: 
http://www.postgresql.org/docs/8.4/interactive/install-procedure.html

Regards,
Frank




Regards
Raghavendra


On Sun, Mar 28, 2010 at 2:06 PM, Gary Doades g...@gpdnet.co.uk wrote:
On 28/03/2010 8:33 AM, Tadipathri Raghu wrote:


Hi Guz,

It is assuming that there are 2400 rows in this table. Probably  
you've deleted some rows from the table leaving just one.


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.

Thanks if we have proper explination on this..
It's not *showing* any rows at all, it's *guessing* 2400 rows  
because you've never analyzed the table. Without any statistics at  
all, postgres will use some form of in-built guess for a table that  
produces reasonable plans under average conditions. As you've  
already seen, once you analyze the table, the guess get's much   
better and therefore would give you a more appropriate plan.


Regards,
Gary.