Message-ID: <4D49A6B9.4060608@2ndquadrant.com>
Date: Wed, 02 Feb 2011 13:47:21 -0500
From: Greg Smith <greg@2ndquadrant.com>
User-Agent: Thunderbird 2.0.0.24 (X11/20100317)
MIME-Version: 1.0
To: Mladen Gogala <mladen.gogala@vmsinfo.com>
CC: Robert Haas <robertmhaas@gmail.com>, Andrew Dunstan <andrew@dunslane.net>,
	Bruce Momjian <bruce@momjian.us>, Tom Lane <tgl@sss.pgh.pa.us>,
	"david@lang.hm" <david@lang.hm>,
	Craig Ringer <craig@postnewspapers.com.au>,
	Vitalii Tymchyshyn <tivv00@gmail.com>,
	"pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: [HACKERS] Slow count(*) again...
References: <201102012247.p11Ml6u02682@momjian.us>
	<4D48914B.4050403@dunslane.net>	<4D489AD1.1010105@vmsinfo.com>
	<AANLkTiknZi97dDwm7sHqy0EL7tpNEP9sY5uPV6AN+q=u@mail.gmail.com>
	<4D499E55.4020107@vmsinfo.com>
In-Reply-To: <4D499E55.4020107@vmsinfo.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Mladen Gogala wrote:
> People are complaining about the optimizer not using the indexes all 
> over the place, there should be a way to
> make the optimizer explicitly prefer the indexes, like was the case 
> with Oracle's venerable RBO (rules based
> optimizer). RBO didn't use statistics, it had a rank of access method 
> and used the access method with the highest
> rank of all available access methods. In practice, it translated into: 
> if an index exists - use it.

Given that even Oracle kicked out the RBO a long time ago, I'm not so 
sure longing for those good old days will go very far.  I regularly see 
queries that were tweaked to always use an index run at 1/10 or less the 
speed of a sequential scan against the same data.  The same people 
complaining "all over the place" about this topic are also the sort who 
write them.  There are two main fallacies at play here that make this 
happen:

1) Even if you use an index, PostgreSQL must still retrieve the 
associated table data to execute the query in order to execute its 
version of MVCC

2) The sort of random I/O done by index lookups can be as much as 50X as 
expensive on standard hard drives as sequential, if every block goes to 
physical hardware.

If I were to work on improving this area, it would be executing on some 
plans a few of us have sketched out for exposing some notion about what 
indexes are actually in memory to the optimizer.  There are more obvious 
fixes to the specific case of temp tables though.

-- 
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books