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..196 73.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 2 2354
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********@spa munieprzyjmuje. wp.pl> wrote in message
news:d0******** **@atlantis.new s.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..196 73.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
In news:Pk******** ********@newsre ad1.news.pas.ea rthlink.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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Hema Sekhar |
last post by:
Hi
I am Hemasekhar K.P
I am trying to replicae pgsql on REDHATLinux 8,
but the site http://gborg.postgresql.org/genpage?replication_72install. is
giving instructions on REDHATLinux 7,
is there any site which gives instructions on how to replicate on
REDHATLinux 8,
Actually in postgresql.conf which is under
/usr/local/pgsql/data/postgresql.conf, I added the following lines
|
by: Daniel E. Fisher |
last post by:
> I can't get a rest for a min guys.
>
> I go away for the weekend and my server is getting this error.
>
> Fatal error: Call to undefined function: pg_connect() in
> /var/www/html/crohns/phpBB2/db/postgres7.php on line 79
Your PHP doesn't have support for PostgreSQL.
Look at phpinfo();
|
by: Keith Bottner |
last post by:
I have been looking for an interactive PL/pgSQL for Postgres with no luck.
Does anybody know of one open source or commercial?
If not then I would like to know if there is currently a debugging API that
can be attached to for writing such a debugger?
And finally...
If there isn't any debugging API how many people are interested in either
the debugging API or the final product which would be an interactive
|
by: Postgresql |
last post by:
Hello,
I am unable to get on many of the postgresql mailing lists.
I usually get this message back with no attachment and no email comes
afterwards with a confirmation.
I've tried this over and over on multiple lists.
After a while I was able to get on pgsql-admin...but it took a few tries.
It's been a couple days now, so I'm assuming majordomo has had time to
process my request.
|
by: Ed L. |
last post by:
Has anyone successfully built 7.3.4, 7.4.3, or 8.0.0beta1 on
IA64 with HP-UX 11i v2 (11.23)? I'm not having any success,
running into 'make' failures. Googling old posts suggests
this might be due to missing test-and-set code for ia64?
I saw a disputed/repudiated ViSolve patch, but nothing else.
Is this still broken?
Here's my attempt on 8.0.0beta1:
$uname -a
| |
by: snpe |
last post by:
Is it down ?
regards
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
|
by: John French |
last post by:
I just installed postgresql 7.4.5 and pgadmin3 1.0.2 on FreeBSD and noticed
that pgadmin allows the pgsql user to log in while ignoring the password.
You can enter a wrong password or no password and still get into postgresql.
I started the postmaster as a FreeBSD user named pgsql by running
postmaster -i -D /usr/local/pgsql/data
I changed postgresql's pgsql user account to have a new password via
pgadmin3 too. But I can still log in to...
|
by: sysxperts |
last post by:
Hello,
Having an issue that is specific to PHP compiled with PGSQL support
with versions noted in subject line. I understand that there are many
variables to consider here but believe I have narrowed down the Apache
Client Cert failures to my PHP/PGSQL build.
1. Apache PHP without PGSQL works as expected using client
certificates
2. Apache PHP with PGSQL works fine with standard SSL connections
3 Apache PHP with PGSQL fails with...
|
by: Matt |
last post by:
I need an installation with Postgres support, and was happy to find in
my phpinfo that there was a
--with-pgsql=shared
option in the config line.
but I still got "call to undefined function: pgsql_connect (4.3.11)
So I figured whatev, I'll put a new installation (PHP5, in fact), but I
always fail config with:
Cannot find libpq-fe.h. Please specify correct Postgresql installation path
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |