[HACKERS] Query Planning time increased 3 times on 7.1 compared to 7.0.3
Hello,
We noticed that after upgrading to 7.1beta[245] the execution time for
some often used queries went up by a factor of 2 or more. Considering
the early beta state I was not alarmed. But since I noticed that
yesterday's snapshot still has the problem, I'd really like to tell you
about it.
Here is one of the queries, it takes about half a second on our computer
(PII 233 with 256MB RAM) to execute and returns typically 1-4 rows via
two index scans with high selectivity. So it looks to me that planning
time outwages execution time by far. 7.0 took about 0.15 seconds (which
is still much).
Here is the query:
explain verbose select gaenge , s . artikelid , text from
schaertabelle s , extartbez e where maschine = int2(109) and
schaerdatum = '2001-01-13' and s . artikelid = e . artikelid and
extartbezid = 1 and bezkomptype = 0 order by textlimit 10;
And the plan for 7.0 and 7.1 (attached).
The data and schema is accessible via
http://home.wtal.de/petig/pg_test.sql.gz
If you omit 'int2(' the index scan collapses into a sequential scan.
(Well known problem with int2 indices)
Christof
Oh, I'll attach the schema, too. So if you just want to take a look at
the table definition you don't have to download the data.
NOTICE: QUERY DUMP:
{ LIMIT :startup_cost 11.70 :total_cost 11.70 :rows 1 :width 22 :qptargetlist ({
TARGETENTRY :resdom { RESDOM :resno 1 :restype 21 :restypmod -1 :resname gaenge
:reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 1
:varattno 4 :vartype 21 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 4}} {
TARGETENTRY :resdom { RESDOM :resno 2 :restype 23 :restypmod -1 :resname artikelid
:reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 1
:varattno 8 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 8}} {
TARGETENTRY :resdom { RESDOM :resno 3 :restype 1043 :restypmod 54 :resname text
:reskey 0 :reskeyop 0 :ressortgroupref 1 :resjunk false } :expr { VAR :varno 2
:varattno 3 :vartype 1043 :vartypmod 54 :varlevelsup 0 :varnoold 2 :varoattno 3}})
:qpqual <> :lefttree { SORT :startup_cost 11.70 :total_cost 11.70 :rows 1 :width 22
:qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 21 :restypmod -1
:resname gaenge :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR
:varno 1 :varattno 4 :vartype 21 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno
4}} { TARGETENTRY :resdom { RESDOM :resno 2 :restype 23 :restypmod -1 :resname
artikelid :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno
1 :varattno 8 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 8}} {
TARGETENTRY :resdom { RESDOM :resno 3 :restype 1043 :restypmod 54 :resname text
:reskey 1 :reskeyop 1071 :ressortgroupref 1 :resjunk false } :expr { VAR :varno 2
:varattno 3 :vartype 1043 :vartypmod 54 :varlevelsup 0 :varnoold 2 :varoattno 3}})
:qpqual <> :lefttree { NESTLOOP :startup_cost 0.00 :total_cost 11.69 :rows 1 :width 22
:qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 21 :restypmod -1
:resname gaenge :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR
:varno 65001 :varattno 1 :vartype 21 :vartypmod -1 :varlevelsup 0 :varnoold 1
:varoattno 4}} { TARGETENTRY :resdom { RESDOM :resno 2 :restype 23 :restypmod -1
:resname artikelid :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr {
VAR :varno 65001 :varattno 2 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1
:varoattno 8}} { TARGETENTRY :resdom { RESDOM :resno 3 :restype 1043 :restypmod 54
:resname text :reskey 0 :reskeyop 0 :ressortgroupref 1 :resjunk false } :expr { VAR
:varno 65000 :varattno 1 :vartype 1043 :vartypmod 54 :varlevelsup 0 :varnoold 2
:varoattno 3}}) :qpqual <> :lefttree { INDEXSCAN :startup_cost 0.00 :total_cost 2.02
:rows 1 :width 6 :qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 21
:restypmod -1 :resname <> :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false }
:expr { VAR :varno 1 :varattno 4 :vartype 21 :vartypmod -1 :varlevelsup 0 :varnoold 1
:varoattno 4}} { TARGETENTRY :resdom { RESDOM :resno 2 :restype 23 :restypmod -1
:resname <> :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR
:varno 1 :varattno 8 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno
8}}) :qpqual <> :lefttree <> :righttree <> :extprm () :locprm () :initplan <> :nprm 0
:scanrelid 1 :indxid ( 2424224) :indxqual (({ EXPR :typeOid 16 :opType op :oper {
OPER :opno 94 :opid 63 :opresulttype 16 } :args ({ VAR :varno 1 :varattno 1 :vartype
21 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 2} { CONST :consttype 21
:constlen 2 :constbyval true :constisnull false :constvalue 2 [ 109 0 0 0 ] })} {
EXPR :typeOid 16 :opType op :oper { OPER :opno 1093 :opid 1086 :opresulttype 16 }
:args ({ VAR :varno 1 :varattno 2 :vartype 1082 :vartypmod -1 :varlevelsup 0
:varnoold 1 :varoattno 3} { CONS
[HACKERS] Getting unique ID through SQL
People will have seen my post on problems with PostgreSQL ODBC driver and MS Access 97. Access 97 has some problems when a record is added that contains a primary key field of type SERIAL. This has something to do with the fact that the value of the primary key is not actually generated until the record is sent to the server. It seems it is easiest for me to get the unique ID from the server myself and insert it into the record when Access creates it. In the realm of file based databases on a local machine it is easy to do this: store the unique variable into a special table, read it out, increment it and store it back. Very quick and there may only ever be one user. Things become different on an SQL server because there may be multiple users simultaneously accessing the database. Two SQL operations are required to retrieve the variable's value and update it: a SELECT and UPDATE. Depending on how fast your connection is, between the SELECT and UPDATE, someone else could have run the same SELECT and got the same value back. Then when both records are sent to the server with duplicate values in the same primary key, one will fail. What I need is some foolproof way of getting and updating the variable in one operation. Is it going to be an Int4 stored in a special table, or can it be a serial? Do I use a stored procedure or what? How do I get its value from Access? Whatever you think of Access, the alternative seems to be clunky PHP forms with lots of code behind them for data entry and editing. === Patrick Dunford, Christchurch, NZ - http://pdunford.godzone.net.nz/ Peter replied, Repent and be baptized, every one of you, in the name of Jesus Christ for the forgiveness of your sins. And you will receive the gift of the Holy Spirit. The promise is for you and your children and for all who are far offfor all whom the Lord our God will call. -- Acts 2:38 http://www.heartlight.org/cgi-shl/todaysverse.cgi?day=20010304 === Created by Mail2Sig - http://pdunford.godzone.net.nz/software/mail2sig/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] How to handle waitingForLock in LockWaitCancel()
Hiroshi Inoue <[EMAIL PROTECTED]> writes: > I sometimes encountered SEGV errors in my test case > when I canceled the execution. Can you provide backtraces from these SEGVs? > Probably it's due to the almost simultaneous arrival > of multiple signals and the following patch seems to > fix the bug. However I'm afraid that the change should > cause another bug. I do not like that change at *all*. In the first place, how could it stop whatever is causing the SEGV? The waitingForLock flag is not examined anywhere else, so unless things are already broken this cannot have any effect. In the second place, postponing the reset of the flag has the potential for an infinite loop, because this routine is called during error exit. Suppose LockLockTable causes an elog(ERROR)? I think we need to look harder to find the cause of the SEGVs you are seeing. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] How to handle waitingForLock in LockWaitCancel()
Hi,
I sometimes encountered SEGV errors in my test case
when I canceled the execution.
Probably it's due to the almost simultaneous arrival
of multiple signals and the following patch seems to
fix the bug. However I'm afraid that the change should
cause another bug.
Comments ?
Regards,
Hiroshi Inoue
Index: proc.c
===
RCS file:
/home/projects/pgsql/cvsroot/pgsql/src/backend/storage/lmgr/proc.c,v
retrieving revision 1.98
diff -c -c -r1.98 proc.c
*** proc.c 2001/01/26 18:23:12 1.98
--- proc.c 2001/03/05 02:28:09
***
*** 327,334
if (!waitingForLock)
return false;
- waitingForLock = false;
-
/* Turn off the deadlock timer, if it's still running (see
ProcSleep) */
#ifndef __BEOS__
{
--- 327,332
***
*** 345,350
--- 343,349
/* Unlink myself from the wait queue, if on it (might not be
anymore!) *
/
LockLockTable();
+ waitingForLock = false;
if (MyProc->links.next != INVALID_OFFSET)
RemoveFromWaitQueue(MyProc);
UnlockLockTable();
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
[HACKERS] Access 97 & PostgreSQL ODBC Driver Problems
pproach has not been totally successful, not sure about the first but will experiment with that. Does anyone have opinions on ways of resolving these issues? === Patrick Dunford, Christchurch, NZ - http://pdunford.godzone.net.nz/ Peter replied, Repent and be baptized, every one of you, in the name of Jesus Christ for the forgiveness of your sins. And you will receive the gift of the Holy Spirit. The promise is for you and your children and for all who are far offfor all whom the Lord our God will call. -- Acts 2:38 http://www.heartlight.org/cgi-shl/todaysverse.cgi?day=20010304 === Created by Mail2Sig - http://pdunford.godzone.net.nz/software/mail2sig/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] Re: why the DB file size does not reduce when 'delete'the data in DB?
> I know your situations, your DB is not updated and inserted lots of records in few minutes, > mine is difference, I have a real time Stock Trading system, you know, stock, its price > is changed every minute or even every second , I need update and insert delta change into DB, > draw their trend graphics, suppose there are 3000 stocks in market, there maybe 9000 records > changed and inserted in one minutes, because PGSQL's storage manager problem( it does not > reuse deleted record space), in 4 hours trading period, my harddisk can be full filled. because in > the period, the table indeed gets very large, doing VACCUME is impossible in realtime, it will lock > out other clients too long time, my point of view is PGSQL is fit for static or small changed database, > not fit for lots of change in short time. It's admitadly a problem so I don't think you need to convince everyone that it's not the best way to handle things :-) I hate to say it, but your options currently are to upgrade your storage device or change databases... I think I'd fork out some cash for some new hardware verses buying a commercial database or putting up with the missing features of MySQL.. All my humble opinion of course, I wish you the best of luck. -Mitch ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Multi-process pgbench?
Matthew Kirkwood <[EMAIL PROTECTED]> writes: > I have access to a couple of 4-CPU boxes, and reckon > that a single-process benching tool could well prove > a bottleneck. It's not, as far as I can tell. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Multi-process pgbench?
Hi, Did I read allegations here a while ago that someone had a multi-process version of pgbench? I've poked around the website and mail archives, but couldn't find it. I have access to a couple of 4-CPU boxes, and reckon that a single-process benching tool could well prove a bottleneck. Matthew. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
