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

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

From: Robert Haas <robertmhaas(at)gmail(dot)com>
To: Mladen Gogala <mladen(dot)gogala(at)vmsinfo(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:20:59
Message-ID: (view raw or whole thread)
Lists: pgsql-hackerspgsql-performance
On Wed, Feb 2, 2011 at 1:11 PM, Mladen Gogala <mladen(dot)gogala(at)vmsinfo(dot)com> wrote:
> 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.

The default selectivity estimates ought to make this happen already.

create temporary table foo (a integer, b text);
insert into foo select g, random()::text||random()::text from
generate_series(1, 10000) g;
INSERT 0 10000
alter table foo add primary key (a);
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index
"foo_pkey" for table "foo"
explain select * from foo where a = 1;
                             QUERY PLAN
 Index Scan using foo_pkey on foo  (cost=0.00..8.27 rows=1 width=36)
   Index Cond: (a = 1)
(2 rows)

You're going to need to come up with actual examples of situations
that you think can be improved upon if you want to get anywhere here.

Robert Haas
The Enterprise PostgreSQL Company

In response to

pgsql-performance by date

Next:From: Greg SmithDate: 2011-02-02 18:22:55
Subject: Re: Configuration for a new server.
Previous:From: Jon NelsonDate: 2011-02-02 18:19:20
Subject: Re: [HACKERS] Slow count(*) again...

pgsql-hackers by date

Next:From: Dimitri FontaineDate: 2011-02-02 18:22:33
Previous:From: Nicolas GrillyDate: 2011-02-02 18:20:15
Subject: Why "copy ... from stdio" does not return immediately when reading invalid data?

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