BUG: PG do not use index
От | |
---|---|
Тема | BUG: PG do not use index |
Дата | |
Msg-id | 01bf01c88e66$24884d60$1200a8c0@kharkov.localhost обсуждение исходный текст |
Ответы |
Re: BUG: PG do not use index
Re: BUG: PG do not use index |
Список | pgsql-bugs |
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;
В списке pgsql-bugs по дате отправления: