pgsql optimizer

[gefek]

Hello,
I've got a server postgresql 7.4.6 installed from RPM for linux RH 9.
Lately, I've noticed, that some queries last too long... It appears to me,
that the optimizer does not use index optimizing....
This is what analyze shows:
------
explain analyze update activities set act_synch_date='2005-02-03 00:00:00'
where activities.act_id=17;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
Seq Scan on activities (cost=0.00..19673.33 rows=2 width=107) (actual
time=1352.235..1352.248 rows=1 loops=1)
Filter: (act_id = 17)
Total runtime: 1354.814 ms
(3 rows)
------
and log for this query:
------
Mar 4 13:05:55 devserver01 postgres[14674]: [4-1] LOG: QUERY STATISTICS
Mar 4 13:05:55 devserver01 postgres[14674]: [4-2] DETAIL: ! system usage
stats:
Mar 4 13:05:55 devserver01 postgres[14674]: [4-3] ^I!^I1.340092 elapsed
0.390000 user 0.170000 system sec
Mar 4 13:05:55 devserver01 postgres[14674]: [4-4] ^I!^I[0.770000 user
0.410000 sys total]
Mar 4 13:05:55 devserver01 postgres[14674]: [4-5] ^I!^I0/0 [0/0]
filesystem blocks in/out
Mar 4 13:05:55 devserver01 postgres[14674]: [4-6] ^I!^I96/3 [8743/182]
page faults/reclaims, 0 [0] swaps
Mar 4 13:05:55 devserver01 postgres[14674]: [4-7] ^I!^I0 [0] signals
rcvd, 0/0 [0/0] messages rcvd/sent
Mar 4 13:05:55 devserver01 postgres[14674]: [4-8] ^I!^I0/0 [0/0]
voluntary/involuntary context switches
Mar 4 13:05:55 devserver01 postgres[14674]: [4-9] ^I! buffer usage stats:
Mar 4 13:05:55 devserver01 postgres[14674]: [4-10] ^I!^IShared blocks:
9659 read, 2 written, buffer hit rate = 0.23%
Mar 4 13:05:55 devserver01 postgres[14674]: [4-11] ^I!^ILocal blocks:
0 read, 0 written, buffer hit rate = 0.00%
Mar 4 13:05:55 devserver01 postgres[14674]: [4-12] ^I!^IDirect blocks:
0 read, 0 written
------
Of course there is index created for act_id.... Why the optimizer does not
use it?
--
Grzegorz Wilk

[Nikolay A Mirin]

Very interesting.
Never seen that.
Can you show your postgresql.conf?
This is the first thing I would look at.
But everything should be fine in the default configuration.

The other reasons I might suppose are
1) the filter doesn't realy reduce the amount of data. I mean most of the
rows satisfy it. And optimizer decides not to use it.

2) the index needs to be rebuilt (REINDEX ) and the database vacuumed
(VACUUM.... )
"gefek" <th********@spamunieprzyjmuje.wp.pl> wrote in message
news:d0**********@atlantis.news.tpi.pl...

Hello,
I've got a server postgresql 7.4.6 installed from RPM for linux RH 9.
Lately, I've noticed, that some queries last too long... It appears to me,
that the optimizer does not use index optimizing....
This is what analyze shows:
------
explain analyze update activities set act_synch_date='2005-02-03 00:00:00'
where activities.act_id=17;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
Seq Scan on activities (cost=0.00..19673.33 rows=2 width=107) (actual
time=1352.235..1352.248 rows=1 loops=1)
Filter: (act_id = 17)
Total runtime: 1354.814 ms
(3 rows)
------
and log for this query:
------
Mar 4 13:05:55 devserver01 postgres[14674]: [4-1] LOG: QUERY STATISTICS
Mar 4 13:05:55 devserver01 postgres[14674]: [4-2] DETAIL: ! system usage
stats:
Mar 4 13:05:55 devserver01 postgres[14674]: [4-3] ^I!^I1.340092 elapsed
0.390000 user 0.170000 system sec
Mar 4 13:05:55 devserver01 postgres[14674]: [4-4] ^I!^I[0.770000 user
0.410000 sys total]
Mar 4 13:05:55 devserver01 postgres[14674]: [4-5] ^I!^I0/0 [0/0]
filesystem blocks in/out
Mar 4 13:05:55 devserver01 postgres[14674]: [4-6] ^I!^I96/3 [8743/182]
page faults/reclaims, 0 [0] swaps
Mar 4 13:05:55 devserver01 postgres[14674]: [4-7] ^I!^I0 [0] signals
rcvd, 0/0 [0/0] messages rcvd/sent
Mar 4 13:05:55 devserver01 postgres[14674]: [4-8] ^I!^I0/0 [0/0]
voluntary/involuntary context switches
Mar 4 13:05:55 devserver01 postgres[14674]: [4-9] ^I! buffer usage stats:
Mar 4 13:05:55 devserver01 postgres[14674]: [4-10] ^I!^IShared blocks:
9659 read, 2 written, buffer hit rate = 0.23%
Mar 4 13:05:55 devserver01 postgres[14674]: [4-11] ^I!^ILocal blocks: 0
read, 0 written, buffer hit rate = 0.00%
Mar 4 13:05:55 devserver01 postgres[14674]: [4-12] ^I!^IDirect blocks: 0
read, 0 written
------
Of course there is index created for act_id.... Why the optimizer does not
use it?
--
Grzegorz Wilk

[gefek]

In news:Pk****************@newsread1.news.pas.earthli nk.net Nikolay A
Mirin wrote:

Very interesting.
Never seen that.
Can you show your postgresql.conf?
This is the first thing I would look at.
But everything should be fine in the default configuration.
The other reasons I might suppose are
1) the filter doesn't realy reduce the amount of data. I mean most of
the rows satisfy it. And optimizer decides not to use it.
2) the index needs to be rebuilt (REINDEX ) and the database vacuumed
(VACUUM.... )

I'm sory, but i didn't have time to write... reindex and vacuum helped, it
uses indexes now.
Thanks for help
--
Grzesiek