From: Mladen Gogala <mladen.gogala@vmsinfo.com>
To: 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:11:33 -0500
Message-ID: <4D499E55.4020107@vmsinfo.com> (raw)
In-Reply-To: <AANLkTiknZi97dDwm7sHqy0EL7tpNEP9sY5uPV6AN+q=u@mail.gmail.com>
References: <201102012247.p11Ml6u02682@momjian.us>
<4D48914B.4050403@dunslane.net>
<4D489AD1.1010105@vmsinfo.com>
<AANLkTiknZi97dDwm7sHqy0EL7tpNEP9sY5uPV6AN+q=u@mail.gmail.com>
Robert Haas wrote:
> On Tue, Feb 1, 2011
> It would be pretty hard to make autoanalyze work on such tables
> without removing some of the performance benefits of having such
> tables in the first place - namely, the local buffer manager. But you
> could ANALYZE them by hand.
>
>
Not necessarily autoanalyze, some default rules for the situations when
stats is not there should be put in place,
like the following:
1) If there is a usable index on the temp table - use it.
2) It there isn't a usable index on the temp table and there is a join,
make the temp table the first table
in the nested loop join.
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.
--
Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
http://www.vmsinfo.com
The Leader in Integrated Media Intelligence Solutions
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: 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: <4D499E55.4020107@vmsinfo.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