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

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);
CREATE TABLE
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"
ALTER TABLE
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
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company