Re: BUG: PG do not use index - Mailing list pgsql-bugs

> The standard question: when was the last time you did a vacuum analyze
> on this table?

Now I done:
1. vacuum full
2. analyze
3. analyze akh_testing_result
4. reindex table akh_testing_result
5. explain select count(*) from akh_testing_result
Aggregate  (cost=206372.95..206372.95 rows=1 width=0)
  ->  Seq Scan on akh_testing_result  (cost=0.00..184804.56 rows=8627356
width=0)

select max(id) from akh_testing_result
8817173

I will try to update from 8.0 to 8.3


----- Original Message -----
From: "Bill Moran" <wmoran@collaborativefusion.com>
To: <Eugen.Konkov@aldec.com>
Cc: <pgsql-bugs@postgresql.org>
Sent: Tuesday, March 25, 2008 4:18 PM
Subject: Re: [BUGS] BUG: PG do not use index


> In response to Eugen.Konkov@aldec.com:
>
>> Why PG do not use index?
>
> The standard question: when was the last time you did a vacuum analyze
> on this table?
>
>>
>> select max(id) from akh_testing_result
>> For MySQL this query take 2-3ms, but Postgres take 132 000ms to execute
>> query.
>>
>> select max(id) from akh_testing_result
>> Query executed in 2,12 minutes, 1 Record(s) Returned
>> --------------------------------------------------------------------
>> max
>> ---------------
>> 8757173
>>
>> As we see table has about 9 000 000 records
>>
>> EXPLAIN select max(id) from akh_testing_result
>> "Aggregate  (cost=204986.95..204986.95 rows=1 width=4)"
>> "  ->  Seq Scan on akh_testing_result  (cost=0.00..183568.56 rows=8567356
>> width=4)"
>>
>> Notice that 'id' field is primary index
>>
>> -- DROP TABLE public.akh_testing_result;
>> CREATE TABLE public.akh_testing_result
>> (
>> id serial NOT NULL,
>> testing_conf_id integer NOT NULL,
>> name varchar(64) NOT NULL,
>> test_group_id integer NOT NULL,
>> test_status_id integer NOT NULL,
>> comment text,
>> bug_commited boolean,
>> best_testing_conf_id integer,
>> best_testing_conf_name varchar(255),
>> test_time integer,
>> physical_memory_peak integer,
>> virtual_memory_peak integer,
>> test_id integer,
>> CONSTRAINT "akh_testing_result_pkey" PRIMARY KEY (id),
>> CONSTRAINT "akh_testing_result_testing_conf_fkey" FOREIGN KEY
>> (testing_conf_id) REFERENCES akh_testing_conf(id) ON UPDATE RESTRICT ON
>> DELETE RESTRICT,
>> CONSTRAINT "akh_testing_result_test_group_fkey" FOREIGN KEY
>> (test_group_id) REFERENCES akh_test_group(id) ON UPDATE RESTRICT ON
>> DELETE RESTRICT,
>> CONSTRAINT "akh_testing_result_test_status_fkey" FOREIGN KEY
>> (test_status_id) REFERENCES akh_properties(id) ON UPDATE RESTRICT ON
>> DELETE RESTRICT,
>> CONSTRAINT "akh_testing_result_test_fkey" FOREIGN KEY (test_id)
>> REFERENCES akh_test(id) ON UPDATE RESTRICT ON DELETE RESTRICT
>> );
>> -- Indexes
>> CREATE INDEX akh_testing_result_testing_conf ON akh_testing_result USING
>> btree (testing_conf_id);
>> -- Owner
>> ALTER TABLE public.akh_testing_result OWNER TO postgres;
>>
>>
>>
>>
>>
>>
>
>
> --
> Bill Moran
> Collaborative Fusion Inc.
> http://people.collaborativefusion.com/~wmoran/
>
> wmoran@collaborativefusion.com
> Phone: 412-422-3463x4023
>
> ****************************************************************
> IMPORTANT: This message contains confidential information and is
> intended only for the individual named. If the reader of this
> message is not an intended recipient (or the individual
> responsible for the delivery of this message to an intended
> recipient), please be advised that any re-use, dissemination,
> distribution or copying of this message is prohibited. Please
> notify the sender immediately by e-mail if you have received
> this e-mail by mistake and delete this e-mail from your system.
> E-mail transmission cannot be guaranteed to be secure or
> error-free as information could be intercepted, corrupted, lost,
> destroyed, arrive late or incomplete, or contain viruses. The
> sender therefore does not accept liability for any errors or
> omissions in the contents of this message, which arise as a
> result of e-mail transmission.
> ****************************************************************