By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,779 Members | 1,271 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,779 IT Pros & Developers. It's quick & easy.

pgsql optimizer

P: n/a
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
Share this Question
Share on Google+
2 Replies

P: n/a
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

P: n/a
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.