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

Re: BUG: PG do not use index

From: <Eugen(dot)Konkov(at)aldec(dot)com>
To: "Bill Moran" <wmoran(at)collaborativefusion(dot)com>
Cc: <pgsql-bugs(at)postgresql(dot)org>
Subject: Re: BUG: PG do not use index
Date: 2008-03-26 08:20:05
Message-ID: 01ff01c88f1a$32dbb3c0$1200a8c0@kharkov.localhost (view raw or flat)
Thread:
Lists: pgsql-bugs
> The standard question: when was the last time you did a vacuum analyze
> on this table?

Never did.

 Fortunately, The Auto-Vacuum Daemon monitors table activity and performs 
VACUUMs when necessary. This eliminates the need for administrators to worry 
about disk space recovery in all but the most unusual cases.
http://www.postgresql.org/docs/8.3/interactive/routine-vacuuming.html

It seems I am using old version.
Need I do something more than just reinstall binaries?
My current version:
C:\Program Files\PostgreSQL\8.0\bin>postgres --version
postgres (PostgreSQL) 8.0.3


Thx for answer.

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


> In response to Eugen(dot)Konkov(at)aldec(dot)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(at)collaborativefusion(dot)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.
> **************************************************************** 


In response to

Responses

pgsql-bugs by date

Next:From: Eugen.KonkovDate: 2008-03-26 08:41:18
Subject: Re: BUG: PG do not use index
Previous:From: Eugen.KonkovDate: 2008-03-26 08:14:49
Subject: Re: BUG: PG do not use index

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