BUG: PG do not use index

Eugen.Konkov@aldec.com wrote:

Why PG do not use index?
select max(id) from akh_testing_result

What PG version are you using? Recent versions should indeed use the
index. Perhaps you just need to upgrade.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

In response to Eugen.Konkov@aldec.com:

Why PG do not use index?

The standard question: when was the last time you did a vacuum analyze
on this table?

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;

--
Bill Moran
Collaborative Fusion Inc.
http://people.collaborativefusion.com/~wmoran/

wmoran@collaborativefusion.com
Phone: 412-422-3463x4023

****************************************************************
IMPORTANT: This message contains confidential information and is
intended only for the individual named. If the reader of this
message is not an intended recipient (or the individual
responsible for the delivery of this message to an intended
recipient), please be advised that any re-use, dissemination,
distribution or copying of this message is prohibited. Please
notify the sender immediately by e-mail if you have received
this e-mail by mistake and delete this e-mail from your system.
E-mail transmission cannot be guaranteed to be secure or
error-free as information could be intercepted, corrupted, lost,
destroyed, arrive late or incomplete, or contain viruses. The
sender therefore does not accept liability for any errors or
omissions in the contents of this message, which arise as a
result of e-mail transmission.
****************************************************************

C:\Program Files\PostgreSQL\8.0\bin>postgres --version
postgres (PostgreSQL) 8.0.3

----- Original Message -----
From: "Alvaro Herrera" <alvherre@commandprompt.com>
To: <Eugen.Konkov@aldec.com>
Cc: <pgsql-bugs@postgresql.org>
Sent: Tuesday, March 25, 2008 1:55 PM
Subject: Re: [BUGS] BUG: PG do not use index

The standard question: when was the last time you did a vacuum analyze
on this table?

Never did.

Fortunately, The Auto-Vacuum Daemon monitors table activity and performs
VACUUMs when necessary. This eliminates the need for administrators to worry
about disk space recovery in all but the most unusual cases.
http://www.postgresql.org/docs/8.3/interactive/routine-vacuuming.html

It seems I am using old version.
Need I do something more than just reinstall binaries?
My current version:
C:\Program Files\PostgreSQL\8.0\bin>postgres --version
postgres (PostgreSQL) 8.0.3

Thx for answer.

----- Original Message -----
From: "Bill Moran" <wmoran@collaborativefusion.com>
To: <Eugen.Konkov@aldec.com>
Cc: <pgsql-bugs@postgresql.org>
Sent: Tuesday, March 25, 2008 4:18 PM
Subject: Re: [BUGS] BUG: PG do not use index

The standard question: when was the last time you did a vacuum analyze
on this table?

Now I done:
1. vacuum full
2. analyze
3. analyze akh_testing_result
4. reindex table akh_testing_result
5. explain select count(*) from akh_testing_result
Aggregate (cost=206372.95..206372.95 rows=1 width=0)
-> Seq Scan on akh_testing_result (cost=0.00..184804.56 rows=8627356
width=0)

select max(id) from akh_testing_result
8817173

I will try to update from 8.0 to 8.3

----- Original Message -----
From: "Bill Moran" <wmoran@collaborativefusion.com>
To: <Eugen.Konkov@aldec.com>
Cc: <pgsql-bugs@postgresql.org>
Sent: Tuesday, March 25, 2008 4:18 PM
Subject: Re: [BUGS] BUG: PG do not use index

On 2008-03-26 09:41, Eugen.Konkov@aldec.com wrote:

5. explain select count(*) from akh_testing_result
Aggregate (cost=206372.95..206372.95 rows=1 width=0)
-> Seq Scan on akh_testing_result (cost=0.00..184804.56 rows=8627356
width=0)

You will always get seq scan when you select count(*) without where:
http://sql-info.de/en/postgresql/postgres-gotchas.html#1_7

select max(id) from akh_testing_result
8817173
I will try to update from 8.0 to 8.3

This will help with select max(id). An update is recommended anyway. And
it is required if you are using Windows, as 8.0 is not supported on
Windows anymore.

Regards
Tometzky
--
...although Eating Honey was a very good thing to do, there was a
moment just before you began to eat it which was better than when you
were...
Winnie the Pooh

Ok. thx for answer.
When upgrading from 8.0 to 8.3
(http://www.postgresql.org/docs/8.3/interactive/install-upgrading.html)

if those error messages are normal?

C:\Program Files\PostgreSQL\8.3\bin>psql -d postgres -U root -W -f
../../pg_dump
new 2>&1 > c:/restore.log
Password for user root:
Password for user root:
psql:../../pg_dumpnew:15: ERROR: role "postgres" already exists
psql:../../pg_dumpnew:17: ERROR: role "root" already exists
Password for user root:
psql:../../pg_dumpnew:88: ERROR: нет доступа к файлу "$libdir/dbsize": No
such
file or directory
psql:../../pg_dumpnew:91: ERROR: функция public.database_size(name) не
существу
ет
psql:../../pg_dumpnew:99: ERROR: нет доступа к файлу "$libdir/dbsize": No
such
file or directory
psql:../../pg_dumpnew:102: ERROR: функция public.pg_database_size(oid) не
сущес
твует
psql:../../pg_dumpnew:110: ERROR: нет доступа к файлу "$libdir/admin": No
such
file or directory
psql:../../pg_dumpnew:113: ERROR: функция public.pg_dir_ls(text, boolean)
не су
ществует
psql:../../pg_dumpnew:132: ERROR: нет доступа к файлу "$libdir/admin": No
such
file or directory
psql:../../pg_dumpnew:135: ERROR: функция public.pg_file_read(text, bigint,
big
int) не существует
psql:../../pg_dumpnew:143: ERROR: нет доступа к файлу "$libdir/admin": No
such
file or directory
psql:../../pg_dumpnew:146: ERROR: функция public.pg_file_rename(text, text,
tex
t) не существует
psql:../../pg_dumpnew:165: ERROR: нет доступа к файлу "$libdir/admin": No
such
file or directory
psql:../../pg_dumpnew:168: ERROR: функция public.pg_file_stat(text) не
существу
ет
psql:../../pg_dumpnew:176: ERROR: нет доступа к файлу "$libdir/admin": No
such
file or directory
psql:../../pg_dumpnew:179: ERROR: функция public.pg_file_unlink(text) не
сущест
вует
psql:../../pg_dumpnew:187: ERROR: нет доступа к файлу "$libdir/admin": No
such
file or directory
psql:../../pg_dumpnew:190: ERROR: функция public.pg_file_write(text, text,
bool
ean) не существует
psql:../../pg_dumpnew:198: ERROR: нет доступа к файлу "$libdir/admin": No
such
file or directory
psql:../../pg_dumpnew:201: ERROR: функция public.pg_logdir_ls() не
существует
psql:../../pg_dumpnew:209: ERROR: нет доступа к файлу "$libdir/admin": No
such
file or directory
psql:../../pg_dumpnew:212: ERROR: функция public.pg_postmaster_starttime()
не с
уществует
psql:../../pg_dumpnew:220: ERROR: нет доступа к файлу "$libdir/dbsize": No
such
file or directory
psql:../../pg_dumpnew:223: ERROR: функция public.pg_relation_size(oid) не
сущес
твует
psql:../../pg_dumpnew:231: ERROR: нет доступа к файлу "$libdir/admin": No
such
file or directory
psql:../../pg_dumpnew:234: ERROR: функция public.pg_reload_conf() не
существует

psql:../../pg_dumpnew:242: ERROR: нет доступа к файлу "$libdir/dbsize": No
such
file or directory
psql:../../pg_dumpnew:245: ERROR: функция public.pg_size_pretty(bigint) не
суще
ствует
psql:../../pg_dumpnew:253: ERROR: нет доступа к файлу "$libdir/dbsize": No
such
file or directory
psql:../../pg_dumpnew:256: ERROR: функция public.pg_tablespace_size(oid) не
сущ
ествует
psql:../../pg_dumpnew:264: ERROR: нет доступа к файлу "$libdir/dbsize": No
such
file or directory
psql:../../pg_dumpnew:267: ERROR: функция public.relation_size(text) не
существ
ует
psql:../../pg_dumpnew:969: ERROR: функция pg_logdir_ls() не существует
СТРОКА 2: SELECT a.filetime, a.filename FROM pg_logdir_ls() a(file...
^
ПОДСКАЗКА: No function matches the given name and argument types. You might
nee
d to add explicit type casts.
psql:../../pg_dumpnew:972: ERROR: отношение "public.pg_logdir_ls" не
существует

Password for user root:
psql:../../pg_dumpnew:2225: ERROR: нет доступа к файлу "$libdir/dbsize": No
suc
h file or directory
psql:../../pg_dumpnew:2228: ERROR: функция public.database_size(name) не
сущест
вует
psql:../../pg_dumpnew:2270: ERROR: нет доступа к файлу "$libdir/dbsize": No
suc
h file or directory
psql:../../pg_dumpnew:2273: ERROR: функция public.pg_database_size(oid) не
суще
ствует
psql:../../pg_dumpnew:2281: ERROR: нет доступа к файлу "$libdir/admin": No
such
file or directory
psql:../../pg_dumpnew:2284: ERROR: функция public.pg_dir_ls(text, boolean)
не с
уществует
psql:../../pg_dumpnew:2303: ERROR: нет доступа к файлу "$libdir/admin": No
such
file or directory
psql:../../pg_dumpnew:2306: ERROR: функция public.pg_file_read(text,
bigint, bi
gint) не существует
psql:../../pg_dumpnew:2314: ERROR: нет доступа к файлу "$libdir/admin": No
such
file or directory
psql:../../pg_dumpnew:2317: ERROR: функция public.pg_file_rename(text,
text, te
xt) не существует
psql:../../pg_dumpnew:2336: ERROR: нет доступа к файлу "$libdir/admin": No
such
file or directory
psql:../../pg_dumpnew:2339: ERROR: функция public.pg_file_stat(text) не
существ
ует
psql:../../pg_dumpnew:2347: ERROR: нет доступа к файлу "$libdir/admin": No
such
file or directory
psql:../../pg_dumpnew:2350: ERROR: функция public.pg_file_unlink(text) не
сущес
твует
psql:../../pg_dumpnew:2358: ERROR: нет доступа к файлу "$libdir/admin": No
such
file or directory
psql:../../pg_dumpnew:2361: ERROR: функция public.pg_file_write(text, text,
boo
lean) не существует
psql:../../pg_dumpnew:2369: ERROR: нет доступа к файлу "$libdir/admin": No
such
file or directory
psql:../../pg_dumpnew:2372: ERROR: функция public.pg_logdir_ls() не
существует
psql:../../pg_dumpnew:2380: ERROR: нет доступа к файлу "$libdir/admin": No
such
file or directory
psql:../../pg_dumpnew:2383: ERROR: функция public.pg_postmaster_starttime()
не
существует
psql:../../pg_dumpnew:2391: ERROR: нет доступа к файлу "$libdir/dbsize": No
suc
h file or directory
psql:../../pg_dumpnew:2394: ERROR: функция public.pg_relation_size(oid) не
суще
ствует
psql:../../pg_dumpnew:2402: ERROR: нет доступа к файлу "$libdir/admin": No
such
file or directory
psql:../../pg_dumpnew:2405: ERROR: функция public.pg_reload_conf() не
существуе
т
psql:../../pg_dumpnew:2413: ERROR: нет доступа к файлу "$libdir/dbsize": No
suc
h file or directory
psql:../../pg_dumpnew:2416: ERROR: функция public.pg_size_pretty(bigint) не
сущ
ествует
psql:../../pg_dumpnew:2424: ERROR: нет доступа к файлу "$libdir/dbsize": No
suc
h file or directory
psql:../../pg_dumpnew:2427: ERROR: функция public.pg_tablespace_size(oid)
не су
ществует
psql:../../pg_dumpnew:2435: ERROR: нет доступа к файлу "$libdir/dbsize": No
suc
h file or directory
psql:../../pg_dumpnew:2438: ERROR: функция public.relation_size(text) не
сущест
вует
psql:../../pg_dumpnew:3505: ERROR: функция pg_logdir_ls() не существует
СТРОКА 2: SELECT a.filetime, a.filename FROM pg_logdir_ls() a(file...
^
ПОДСКАЗКА: No function matches the given name and argument types. You might
nee
d to add explicit type casts.
psql:../../pg_dumpnew:3508: ERROR: отношение "public.pg_logdir_ls" не
существуе
т
psql:../../pg_dumpnew:9213484: ERROR: язык "plpgsql" уже существует
psql:../../pg_dumpnew:9213495: ERROR: нет доступа к файлу "$libdir/dbsize":
No
such file or directory
psql:../../pg_dumpnew:9213498: ERROR: функция public.database_size(name) не
сущ
ествует
psql:../../pg_dumpnew:9213506: ERROR: нет доступа к файлу "$libdir/dbsize":
No
such file or directory
psql:../../pg_dumpnew:9213509: ERROR: функция public.pg_database_size(oid)
не с
уществует
psql:../../pg_dumpnew:9213517: ERROR: нет доступа к файлу "$libdir/admin":
No s
uch file or directory
psql:../../pg_dumpnew:9213520: ERROR: функция public.pg_dir_ls(text,
boolean) н
е существует
psql:../../pg_dumpnew:9213539: ERROR: нет доступа к файлу "$libdir/admin":
No s
uch file or directory
psql:../../pg_dumpnew:9213542: ERROR: функция public.pg_file_read(text,
bigint,
bigint) не существует
psql:../../pg_dumpnew:9213550: ERROR: нет доступа к файлу "$libdir/admin":
No s
uch file or directory
psql:../../pg_dumpnew:9213553: ERROR: функция public.pg_file_rename(text,
text,
text) не существует
psql:../../pg_dumpnew:9213572: ERROR: нет доступа к файлу "$libdir/admin":
No s
uch file or directory
psql:../../pg_dumpnew:9213575: ERROR: функция public.pg_file_stat(text) не
суще
ствует
psql:../../pg_dumpnew:9213583: ERROR: нет доступа к файлу "$libdir/admin":
No s
uch file or directory
psql:../../pg_dumpnew:9213586: ERROR: функция public.pg_file_unlink(text)
не су
ществует
psql:../../pg_dumpnew:9213594: ERROR: нет доступа к файлу "$libdir/admin":
No s
uch file or directory
psql:../../pg_dumpnew:9213597: ERROR: функция public.pg_file_write(text,
text,
boolean) не существует
psql:../../pg_dumpnew:9213605: ERROR: нет доступа к файлу "$libdir/admin":
No s
uch file or directory
psql:../../pg_dumpnew:9213608: ERROR: функция public.pg_logdir_ls() не
существу
ет
psql:../../pg_dumpnew:9213616: ERROR: нет доступа к файлу "$libdir/admin":
No s
uch file or directory
psql:../../pg_dumpnew:9213619: ERROR: функция
public.pg_postmaster_starttime()
не существует
psql:../../pg_dumpnew:9213627: ERROR: нет доступа к файлу "$libdir/dbsize":
No
such file or directory
psql:../../pg_dumpnew:9213630: ERROR: функция public.pg_relation_size(oid)
не с
уществует
psql:../../pg_dumpnew:9213638: ERROR: нет доступа к файлу "$libdir/admin":
No s
uch file or directory
psql:../../pg_dumpnew:9213641: ERROR: функция public.pg_reload_conf() не
сущест
вует
psql:../../pg_dumpnew:9213649: ERROR: нет доступа к файлу "$libdir/dbsize":
No
such file or directory
psql:../../pg_dumpnew:9213652: ERROR: функция public.pg_size_pretty(bigint)
не
существует
psql:../../pg_dumpnew:9213660: ERROR: нет доступа к файлу "$libdir/dbsize":
No
such file or directory
psql:../../pg_dumpnew:9213663: ERROR: функция
public.pg_tablespace_size(oid) не
существует
psql:../../pg_dumpnew:9213671: ERROR: нет доступа к файлу "$libdir/dbsize":
No
such file or directory
psql:../../pg_dumpnew:9213674: ERROR: функция public.relation_size(text) не
сущ
ествует

----- Original Message -----
From: "Tomasz Ostrowski" <tometzky@batory.org.pl>
To: <Eugen.Konkov@aldec.com>
Cc: <pgsql-bugs@postgresql.org>
Sent: Wednesday, March 26, 2008 11:08 AM
Subject: Re: [BUGS] BUG: PG do not use index

In response to Eugen.Konkov@aldec.com:

The standard question: when was the last time you did a vacuum analyze
on this table?

Never did.

That's your problem. Without updated statistics on that table, PostgreSQL
probably thinks that it's so small that an index scan wouldn't be any
faster.

Fortunately, The Auto-Vacuum Daemon monitors table activity and performs
VACUUMs when necessary. This eliminates the need for administrators to worry
about disk space recovery in all but the most unusual cases.
http://www.postgresql.org/docs/8.3/interactive/routine-vacuuming.html

Have you verified that this is running correctly (will be information
in the log files each time it runs). Have you verified that the settings
are aggressive enough to be processing this particular table often enough.
The easiest way to test this is to run an EXPLAIN ANALYZE on the query,
then manually VACUUM ANALYZE the table, then run another EXPLAIN ANALYZE.
If the differences in times and statistics between the two EXPLAINs is
significant, then autovacuum probably isn't doing enough. Also, if it
turns out that autovacuum isn't cutting it, you'll probably need to run
VACUUM FULL and REINDEX on the whole database to get things back under
control.

It seems I am using old version.
Need I do something more than just reinstall binaries?
My current version:
C:\Program Files\PostgreSQL\8.0\bin>postgres --version
postgres (PostgreSQL) 8.0.3

You can upgrade to 8.0.15 simply by reinstalling, restarting the postmaster
and running a REINDEX (the REINDEX may not be required, see the release
notes for 8.0.6):
http://www.postgresql.org/docs/8.0/static/release-8-0-6.html

However, the 8.0 series is lacking a lot of improvements. If you can
spare some downtime, I highly recommend you upgrade to 8.2.7. This is
a bit more work though, because you'll have to dump your database, then
reinstall PG, then restore the data into a freshly created cluster.

In any event, make sure you have a good backup before doing either
upgrade.

--
Bill Moran
Collaborative Fusion Inc.
http://people.collaborativefusion.com/~wmoran/

wmoran@collaborativefusion.com
Phone: 412-422-3463x4023