Thread: BUG: PG do not use index

BUG: PG do not use index

From

Date:

25 March 2008, 10:51:27

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;

Re: BUG: PG do not use index

From

Alvaro Herrera

Date:

25 March 2008, 11:55:13

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.

Re: BUG: PG do not use index

From

Bill Moran

Date:

25 March 2008, 14:18:45

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
UPDATERESTRICT ON DELETE RESTRICT, 
> CONSTRAINT "akh_testing_result_test_group_fkey" FOREIGN KEY (test_group_id) REFERENCES akh_test_group(id) ON UPDATE
RESTRICTON DELETE RESTRICT, 
> CONSTRAINT "akh_testing_result_test_status_fkey" FOREIGN KEY (test_status_id) REFERENCES akh_properties(id) ON UPDATE
RESTRICTON 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.
****************************************************************

Re: BUG: PG do not use index

From

Date:

26 March 2008, 08:15:03

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


> 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.

Re: BUG: PG do not use index

From

Date:

26 March 2008, 08:20:12

> 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


> 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.
> ****************************************************************

Re: BUG: PG do not use index

From

Date:

26 March 2008, 08:41:25

> 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


> 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.
> ****************************************************************

Re: BUG: PG do not use index

From

Tomasz Ostrowski

Date:

26 March 2008, 09:08:15

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

Re: BUG: PG do not use index

From

Date:

26 March 2008, 09:45:35

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


> 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
>

Re: BUG: PG do not use index

From

Bill Moran

Date:

26 March 2008, 13:22:23

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