BUG: PG do not use index

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;

Responses

Browse pgsql-bugs by date

  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