int8, primary key, seq scan

Поиск
Список
Период
Сортировка
От Jeff Amiel
Тема int8, primary key, seq scan
Дата
Msg-id 41251533.80500@istreamimaging.com
обсуждение исходный текст
Ответы Re: int8, primary key, seq scan  (Dave Cramer <pg@fastcrypt.com>)
Re: int8, primary key, seq scan  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-general
7.4.2 running on FreeBSD 5.4.2

I have a table (called "draft" ) that has a bigserial as the primary key.

"check_id bigserial NOT NULL"

I  do NOT have an additional index on that column.

I have a plpgsql function (stored procedure) that selects from that
table based on the primary key field

"select into draftrow * from draft where check_id=draftid;"
where draft id is declared in the DECLARE section as "draftid int8;"

This query is woefully slow.  Sequential scan on table

However, if I modify the query as follows:

"select into draftrow * from draft where check_id=int8(draftid);"

Ultra mega speed improvement. Index scan using the primary key.

I declared the compared value (draftid) as an int8, why should I have to
cast it as such in the query to cause the optimizer to use the primary key?







В списке pgsql-general по дате отправления:

Предыдущее
От: "peter@remindex"
Дата:
Сообщение: Re: select count(*) from pg_stat_activity in V8.0.0
Следующее
От: Kevin Matthews
Дата:
Сообщение: need help