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" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
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 [EMAIL PROTECTED]:
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/
[EMAIL PROTECTED]
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.
****************************************************************
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
