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 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;
