Why PG do not use index?
select max(id) from akh_testing_result
For MySQL this query take 2-3ms, but Postgres take 132 000ms to execute que=
ry.
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=3D204986.95..204986.95 rows=3D1 width=3D4)"
" -> Seq Scan on akh_testing_result (cost=3D0.00..183568.56 rows=3D85673=
56 width=3D4)"
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_i=
d) 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 bt=
ree (testing_conf_id);
-- Owner
ALTER TABLE public.akh_testing_result OWNER TO postgres;