From: | <Eugen(dot)Konkov(at)aldec(dot)com> |
---|---|
To: | <pgsql-bugs(at)postgresql(dot)org> |
Subject: | BUG: PG do not use index |
Date: | 2008-03-25 10:51:11 |
Message-ID: | 01bf01c88e66$24884d60$1200a8c0@kharkov.localhost |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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 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;
From | Date | Subject | |
---|---|---|---|
Next Message | Reginald Drake | 2008-03-25 11:01:44 | BUG #4057: SUM returns NULL when given no rows |
Previous Message | Eugen.Konkov | 2008-03-25 07:59:07 | Re: BUG #4047: case preserve for columns |