Re: [PERFORM] Optimizer Selecting Incorrect Index
On Wed, 25 Aug 2004, Richard Huxton wrote: Index Scan using trptserc on trans (cost=0.00..465.10 rows=44 width=118) Index Cond: (trn_patno = 19) Filter: ((trn_old_date = '1994-08-23'::date) AND (trn_old_date = '2004-08-23'::date) AND (trn_bill_inc = 'B'::bpchar)) (687 rows) Index Scan using todate on trans (cost=0.00..105165.74 rows=1 width=143) Index Cond: ((trn_old_date = '1994-08-23'::date) AND (trn_old_date = '2004-08-23'::date)) Filter: ((trn_patno = 19) AND (trn_bill_inc = 'B'::bpchar)) (713 rows) These queries are different. The first returns 687 rows and the second 713 rows. The 687 and 713 are the number of rows in the plan, not the number of rows the queries return. -- /Dennis Björklund ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Optimizer Selecting Incorrect Index
Dennis Bjorklund wrote: On Wed, 25 Aug 2004, Richard Huxton wrote: These queries are different. The first returns 687 rows and the second 713 rows. The 687 and 713 are the number of rows in the plan, not the number of rows the queries return. D'OH! Thanks Dennis -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Optimizer Selecting Incorrect Index
Tom, your suspicions were correct - ANALYZE was not being run. I run vacuumdb via a cron script during off hours. After checking the scripts on both systems, I found that on the system that was not functioning correctly that the '-z' (analyze) command line option to vacuumdb was missing. After correcting it and re-running the script, the poorly performing SQL query takes only a few seconds as opposed to 15 minutes. Thank you for your help! - David -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Tom Lane Sent: Wednesday, August 25, 2004 3:08 PM To: Richard Huxton Cc: David Price; [EMAIL PROTECTED] Subject: Re: [PERFORM] Optimizer Selecting Incorrect Index Richard Huxton [EMAIL PROTECTED] writes: Things to check: 1. postgresql.conf settings match - different costs could cause this 2. statistics on the two columns (trn_patno,trn_old_date) - if they differ considerably between systems that would also explain it. The different estimated row counts could only come from #2. I suspect David has forgotten to run ANALYZE on the second system. I agree that EXPLAIN VERBOSE output is not helpful... 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[PERFORM] Optimizer Selecting Incorrect Index
I have 2 servers both with the exact same data, the same O.S., the same
version of Postgres (7.4.5) and the exact same db schema's (one production
server, one development server). One server is using the correct index for
SQL queries resulting in extremely slow performance, the other server is
properly selecting the index to use and performance is many times better. I
have tried vacuum, but that did not work. I finally resorted to dumping the
data, removing the database completely, creating a new database and
importing the data only to have to problem resurface. The table has
5,000,000+ rows on both the systems.
When I run 'analyze verbose' on the correctly working system, the following
is displayed:
{INDEXSCAN
:startup_cost 0.00
:total_cost 465.10
:plan_rows 44
:plan_width 118
:targetlist (
{TARGETENTRY
:resdom
{RESDOM
:resno 1
:restype 23
:restypmod -1
:resname trn_integer
:ressortgroupref 0
:resorigtbl 789839
:resorigcol 1
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 1
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 1
}
}
{TARGETENTRY
:resdom
{RESDOM
:resno 2
:restype 23
:restypmod -1
:resname trn_patno
:ressortgroupref 0
:resorigtbl 789839
:resorigcol 2
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 2
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 2
}
}
{TARGETENTRY
:resdom
{RESDOM
:resno 3
:restype 1042
:restypmod 5
:resname trn_bill_inc
:ressortgroupref 0
:resorigtbl 789839
:resorigcol 3
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 3
:vartype 1042
:vartypmod 5
:varlevelsup 0
:varnoold 1
:varoattno 3
}
}
{TARGETENTRY
:resdom
{RESDOM
:resno 4
:restype 1043
:restypmod 13
:resname trn_userid
:ressortgroupref 0
:resorigtbl 789839
:resorigcol 4
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 4
:vartype 1043
:vartypmod 13
:varlevelsup 0
:varnoold 1
:varoattno 4
}
}
{TARGETENTRY
:resdom
{RESDOM
:resno 5
:restype 23
:restypmod -1
:resname trn_location
:ressortgroupref 0
:resorigtbl 789839
:resorigcol 5
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 5
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 5
}
}
{TARGETENTRY
:resdom
{RESDOM
:resno 6
:restype 1082
:restypmod -1
:resname trn_date
:ressortgroupref 0
:resorigtbl 789839
:resorigcol 6
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 6
:vartype 1082
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 6
}
}
{TARGETENTRY
:resdom
{RESDOM
:resno 7
:restype 23
:restypmod -1
:resname trn_sercode
:ressortgroupref 0
:resorigtbl 789839
:resorigcol 7
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 7
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 7
}
}
{TARGETENTRY
:resdom
{RESDOM
:resno 8
:restype 1043
:restypmod 28
:resname trn_descr
:ressortgroupref 0
:resorigtbl 789839
:resorigcol 8
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 8
:vartype 1043
:vartypmod 28
:varlevelsup 0
:varnoold 1
:varoattno 8
}
}
{TARGETENTRY
:resdom
{RESDOM
:resno 9
:restype 23
:restypmod -1
:resname trn_employr
:ressortgroupref 0
:resorigtbl 789839
:resorigcol 9
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 9
Re: [PERFORM] Optimizer Selecting Incorrect Index
Richard Huxton [EMAIL PROTECTED] writes: Things to check: 1. postgresql.conf settings match - different costs could cause this 2. statistics on the two columns (trn_patno,trn_old_date) - if they differ considerably between systems that would also explain it. The different estimated row counts could only come from #2. I suspect David has forgotten to run ANALYZE on the second system. I agree that EXPLAIN VERBOSE output is not helpful... 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
