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
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
