From: Greg Smith <greg@2ndquadrant.com>
To: Mladen Gogala <mladen.gogala@vmsinfo.com>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: Andrew Dunstan <andrew@dunslane.net>
Cc: Bruce Momjian <bruce@momjian.us>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: david@lang.hm <david@lang.hm>
Cc: Craig Ringer <craig@postnewspapers.com.au>
Cc: Vitalii Tymchyshyn <tivv00@gmail.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: [HACKERS] Slow count(*) again...
Date: Wed, 02 Feb 2011 13:47:21 -0500
Message-ID: <4D49A6B9.4060608@2ndquadrant.com> (raw)
In-Reply-To: <4D499E55.4020107@vmsinfo.com>
References: <201102012247.p11Ml6u02682@momjian.us>
<4D48914B.4050403@dunslane.net>
<4D489AD1.1010105@vmsinfo.com>
<AANLkTiknZi97dDwm7sHqy0EL7tpNEP9sY5uPV6AN+q=u@mail.gmail.com>
<4D499E55.4020107@vmsinfo.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: greg@2ndquadrant.com, mladen.gogala@vmsinfo.com, robertmhaas@gmail.com, andrew@dunslane.net, bruce@momjian.us, tgl@sss.pgh.pa.us, david@lang.hm, craig@postnewspapers.com.au, tivv00@gmail.com
Subject: Re: [HACKERS] Slow count(*) again...
In-Reply-To: <4D49A6B9.4060608@2ndquadrant.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox