Re: 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.
****************************************************************