Skip site navigation (1) Skip section navigation (2)

Re: [HACKERS] Slow count(*) again...

From: Mladen Gogala <mladen(dot)gogala(at)vmsinfo(dot)com>
To: Robert Haas <robertmhaas(at)gmail(dot)com>
Cc: Andrew Dunstan <andrew(at)dunslane(dot)net>, Bruce Momjian <bruce(at)momjian(dot)us>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, "david(at)lang(dot)hm" <david(at)lang(dot)hm>, Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>, Vitalii Tymchyshyn <tivv00(at)gmail(dot)com>, "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: [HACKERS] Slow count(*) again...
Date: 2011-02-02 18:11:33
Message-ID: 4D499E55.4020107@vmsinfo.com (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-performance
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




In response to

Responses

pgsql-performance by date

Next:From: Cesar ArrietaDate: 2011-02-02 18:15:22
Subject: Server Configuration
Previous:From: Benjamin KrajmalnikDate: 2011-02-02 17:46:06
Subject: Re: Configuration for a new server.

pgsql-hackers by date

Next:From: Aidan Van DykDate: 2011-02-02 18:14:35
Subject: Re: ALTER EXTENSION UPGRADE, v3
Previous:From: David E. WheelerDate: 2011-02-02 18:07:59
Subject: Re: ALTER EXTENSION UPGRADE, v3

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group