470,580 Members | 2,327 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,580 developers. It's quick & easy.

pgsql optimizer

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

Jul 19 '05 #1
2 2239
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


Jul 19 '05 #2
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

Jul 19 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Hema Sekhar | last post: by
11 posts views Thread by Daniel E. Fisher | last post: by
3 posts views Thread by Keith Bottner | last post: by
7 posts views Thread by snpe | last post: by
reply views Thread by Matt | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.