Re: [HACKERS] Optimizer bug??
Hi All, EXPLAIN ANALYZE SELECT COUNT(*) AS c FROM articletbl WHERE (tarih BETWEEN '2004-04-24' AND '2004-05-24') result is : QUERY PLAN Aggregate (cost=40987.02..40987.02 rows=1 width=0) (actual time=39209.476..39209.478 rows=1 loops=1) - Seq Scan on articletbl (cost=0.00..40947.39 rows=15851 width=0) (actual time=1233.369..39153.741 rows=19515 loops=1) Filter: ((tarih = '2004-04-24'::date) AND (tarih = '2004-05-24'::date)) Total runtime: 39210.077 ms I use fedora core 1 SMP kernel Configuration : Dual PIII-550 Dual SCSI (10ms. access time i think) 3x128 mb. SDRAM Regards Ismail Kizir - Original Message - From: Stephan Szabo [EMAIL PROTECTED] To: Ismail Kizir [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Monday, May 24, 2004 11:52 PM Subject: Re: [HACKERS] Optimizer bug?? On Mon, 24 May 2004, Ismail Kizir wrote: Hi all, give us the result of these queries: SELECT COUNT(*) FROM articletbl; 268726 records, it takes 34169 ms. to compute this SELECT COUNT(*) AS c FROM articletbl WHERE mydate BETWEEN '2004-04-24' AND '2004-05-24'; 18982 records, it takes 34249 ms. to compute this. SELECT COUNT(*) AS c FROM articletbl WHERE mydate = '2004-04-24'; 850 records, it takes only 40 ms. to compute this. It is evident that there is a problem here! Am I wrong?? What does explain analyze show for the between query (not just explain) and what does it show if enable_seqscan is set to false? It's possible that it's badly overestimating the cost of the range query, but that's hard to say at this point. There is a point at which in general an index scan becomes more costly than a sequence scan, and it's possible to move that point by changing optimizer settings in the configuration. ---(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] Optimizer bug??
Gaetano, I've changed my settings as : #fsync = true # turns forced synchronization on or off #wal_sync_method = fsync# the default varies across platforms: #effective_cache_size = 1000# typically 8KB each random_page_cost = 2# units are one sequential page fetch cost cpu_tuple_cost = 0.009 # (same) cpu_index_tuple_cost = 0.0009 # (same) cpu_operator_cost = 0.0025 # (same) # fsync, fdatasync, open_sync, or open_datasync #wal_buffers = 8# min 4, 8KB each But it still doesn't optimize for that range. Finally, i've set seq_scan off and, it works now. But i think, there must be a way to handle those settings automatically for cpu, ram and hdd settings(is it a sweet dream??) Thanks Ismail Kizir - Original Message - From: Gaetano Mendola [EMAIL PROTECTED] Newsgroups: comp.databases.postgresql.hackers To: Ismail Kizir [EMAIL PROTECTED] Sent: Tuesday, May 25, 2004 12:47 AM Subject: Re: Optimizer bug?? Ismail Kizir wrote: Hi all, give us the result of these queries: SELECT COUNT(*) FROM articletbl; 268726 records, it takes 34169 ms. to compute this SELECT COUNT(*) AS c FROM articletbl WHERE mydate BETWEEN '2004-04-24' AND '2004-05-24'; 18982 records, it takes 34249 ms. to compute this. SELECT COUNT(*) AS c FROM articletbl WHERE mydate = '2004-04-24'; 850 records, it takes only 40 ms. to compute this. It is evident that there is a problem here! Am I wrong?? Try to do an explain analyze for both queries, and repeat the explain analyze but before disable the sequential scan ( set enable_seq_scan=off ). For sure what do you have is extimated cost higher, what do you have to do is decrease that cost, try with these values: random_page_cost = 2.5 cpu_tuple_cost = 0.005 cpu_index_tuple_cost = 0.0005 cpu_operator_cost = 0.0025 these are values that are good for a modern machine. Hackers, what about to decrease the default values for this quantities ? Regards Gaetano Mendola ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Optimizer bug??
Thanks Josh, I'll subscribe to that group. Regards Ismail Kizir - Original Message - From: Josh Berkus [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: Ismail Kizir [EMAIL PROTECTED] Sent: Tuesday, May 25, 2004 8:34 PM Subject: Re: [HACKERS] Optimizer bug?? Gaetano, Hackers, what about to decrease the default values for this quantities ? Oh, I don't think Ismail has a bug, I think he probably just needs to tune his database. Ismail, join the PGSQL-PERFORMANCE mailing list and discuss this over there. -HACKERS is not the appropriate place for a tuning discussion. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Optimizer Bug issue
Tom, You say that default values for optimizer are efficient. But this is evident that it doesn'tfunction as expectedin my case. I have a database of 20 tables, ~1gb total size. My biggest table contains ~270,000 newspaper article from Turkish journals. I am actually working on "fulltext search" programof my own. There is a problem here. Ifthat values arenot designed for an "ordinary" database application. I am asking to myself for which case they are designed for. I didn't write to "hackers" list only for myself, but for everyone of us too. Please examine the values in my messages in past 2 days, you'll see what i mean. I am sure default values are erronous. Regards Ismail Kizir
[HACKERS] Optimizer bug??
Hi everybody,1 EXPLAIN SELECT COUNT(*) AS c FROM articletbl WHERE ((mydate BETWEEN '2004-04-24' AND '2004-05-24' ))2 EXPLAIN SELECT COUNT(*) AS c FROM articletbl WHERE ((mydate = '2004-04-24'))(I ran VACUUM ANALYZE before running those)mydate is an indexed date column.The optimizer optimizes the second query but, it doesn't optimize the firstone and decides to make a "sequential scan".Is this a bug?Or may someone explain me the reason?Thanks in advance.Ismail Kizir
Re: [HACKERS] Optimizer bug??
2003-05-26;141 2003-05-25;138 2003-05-24;110 2003-05-23;132 2003-05-22;144 2003-05-21;157 2003-05-20;156 2003-05-19;131 2003-05-18;120 2003-05-17;130 2003-05-16;128 2003-05-15;157 2003-05-14;149 2003-05-13;158 2003-05-12;135 2003-05-11;143 2003-05-10;123 2003-05-09;128 2003-05-08;171 2003-05-07;177 2003-05-06;167 2003-05-05;151 2003-05-04;146 2003-05-03;130 2003-05-02;125 2003-05-01;156 2003-04-30;212 2003-04-29;206 2003-04-28;175 2003-04-27;172 2003-04-26;171 2003-04-25;205 2003-04-24;206 2003-04-23;179 2003-04-22;206 2003-04-21;163 2003-04-20;175 2003-04-19;162 2003-04-18;182 2003-04-17;210 2003-04-16;199 2003-04-15;196 2003-04-14;168 2003-04-13;158 2003-04-12;162 2003-04-11;175 2003-04-10;204 2003-04-09;203 2003-04-08;207 2003-04-07;160 2003-04-06;177 2003-04-05;140 2003-04-04;162 2003-04-03;173 2003-04-02;173 2003-04-01;186 2003-03-31;160 2003-03-30;154 2003-03-29;161 2003-03-28;170 2003-03-27;177 2003-03-26;200 2003-03-25;191 2003-03-24;153 2003-03-23;146 2003-03-22;161 2003-03-21;152 2003-03-20;171 2003-03-19;167 2003-03-18;207 2003-03-17;154 2003-03-16;149 2003-03-15;168 2003-03-14;172 2003-03-13;199 2003-03-12;182 2003-03-11;192 2003-03-10;170 2003-03-09;173 2003-03-08;169 2003-03-07;184 2003-03-06;186 2003-03-05;186 2003-03-04;208 2003-03-03;177 2003-03-02;160 2003-03-01;181 2003-02-28;162 2003-02-27;197 2003-02-26;186 2003-02-25;191 2003-02-24;182 2003-02-23;179 2003-02-22;172 2003-02-21;187 2003-02-20;189 2003-02-19;181 2003-02-18;204 2003-02-17;175 2003-02-16;155 2003-02-15;157 2003-02-14;141 2003-02-13;165 2003-02-12;166 2003-02-11;142 2003-02-10;132 2003-02-09;154 2003-02-08;142 2003-02-07;144 2003-02-06;154 2003-02-05;197 2003-02-04;189 2003-02-03;159 2003-02-02;114 2003-02-01;132 2003-01-31;182 2003-01-30;175 2003-01-29;181 2003-01-28;186 2003-01-27;160 2003-01-26;153 2003-01-25;149 2003-01-24;180 2003-01-23;171 2003-01-22;170 2003-01-21;183 2003-01-20;171 2003-01-19;163 2003-01-18;154 2003-01-17;157 2003-01-16;165 2003-01-15;159 2003-01-14;192 2003-01-13;148 2003-01-12;163 2003-01-11;161 2003-01-10;185 2003-01-09;142 2003-01-08;186 2003-01-07;184 2003-01-06;176 2003-01-05;158 2003-01-04;160 2003-01-03;167 2003-01-02;162 2003-01-01;148 2002-12-31;166 2002-12-30;155 2002-12-29;129 2002-12-28;177 2002-12-27;152 2002-12-26;176 2002-12-25;159 2002-12-24;191 2002-12-23;163 2002-12-22;148 2002-12-21;148 2002-12-20;175 2002-12-19;193 2002-12-18;201 2002-12-17;219 2002-12-16;192 2002-12-15;179 2002-12-14;186 2002-12-13;156 2002-12-12;181 2002-12-11;206 2002-12-10;190 2002-12-09;155 2002-12-08;151 2002-12-07;150 2002-12-06;163 2002-12-05;156 2002-12-04;194 2002-12-03;194 2002-12-02;166 2002-12-01;154 2002-11-30;191 2002-11-29;163 2002-11-28;179 2002-11-27;191 2002-11-26;184 2002-11-25;174 2002-11-24;129 2002-11-23;167 2002-11-22;170 2002-11-21;206 2002-11-20;200 2002-11-19;195 2002-11-18;144 2002-11-17;177 2002-11-16;161 2002-11-15;195 2002-11-14;221 2002-11-13;192 2002-11-12;199 2002-11-11;166 2002-11-10;157 2002-11-09;145 2002-11-08;197 2002-11-07;202 2002-11-06;168 2002-11-05;155 2002-11-04;151 2002-11-03;144 2002-11-02;188 2002-11-01;191 2002-10-31;189 2002-10-30;196 2002-10-29;193 2002-10-28;168 2002-10-27;155 2002-10-26;155 2002-10-25;184 2002-10-24;205 2002-10-23;187 2002-10-22;194 2002-10-21;202 Ismail Kizir - Original Message - From: Hans-Jürgen Schönig [EMAIL PROTECTED] To: Ismail Kizir [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Monday, May 24, 2004 8:17 PM Subject: Re: [HACKERS] Optimizer bug?? Ismail Kizir wrote: Hi everybody, 1 EXPLAIN SELECT COUNT(*) AS c FROM articletbl WHERE ((mydate BETWEEN '2004-04-24' AND '2004-05-24' ) ) 2 EXPLAIN SELECT COUNT(*) AS c FROM articletbl WHERE ((mydate = '2004-04-24') ) (I ran VACUUM ANALYZE before running those) mydate is an indexed date column. The optimizer optimizes the second query but, it doesn't optimize the first one and decides to make a sequential scan. Is this a bug? Or may someone explain me the reason? Thanks in advance. Ismail Kizir If 2004-04-24 to 2004-05-24 make up let's say 90% of your data PostgreSQL will find out that it is cheaper to use a seq scan instead of an index. This is not a bug at all - this is normal and desired behaviour ... Regards, Hans -- Cybertec Geschwinde u Schoenig Schoengrabern 134, A-2020 Hollabrunn, Austria Tel: +43/720/10 1234567 or +43/664/233 90 75 www.cybertec.at, www.postgresql.at, kernel.cybertec.at ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Optimizer bug??
Alvaro, For the query : EXPLAIN SELECT count(tarih) AS c FROM articletbl WHERE sitekodu = 12 AND ((tarih='2004-04-24' AND tarih'2004-05-24' )) QUERY PLAN is Aggregate (cost=41619.34..41619.34 rows=1 width=4) - Seq Scan on articletbl (cost=0.00..41618.46 rows=353 width=4) Filter: ((sitekodu = 12) AND (tarih = '2004-04-24'::date) AND (tarih '2004-05-24'::date)) And for the query : EXPLAIN SELECT count(tarih) AS c FROM articletbl WHERE sitekodu = 12 AND ((tarih='2004-05-10' AND tarih'2004-05-24' )) QUERY PLAN Aggregate (cost=20279.72..20279.72 rows=1 width=4) - Index Scan using ind_articletbltrh on articletbl (cost=0.00..20279.40 rows=127 width=4) Index Cond: ((tarih = '2004-05-10'::date) AND (tarih '2004-05-24'::date)) Filter: (sitekodu = 12) Have you got an idea? Thanks in advance Ismail Kizir - Original Message - From: Alvaro Herrera [EMAIL PROTECTED] To: Ismail Kizir [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Monday, May 24, 2004 9:23 PM Subject: Re: [HACKERS] Optimizer bug?? On Mon, May 24, 2004 at 08:27:01PM +0300, Ismail Kizir wrote: The optimizer does an indexed scan up to 20 days, and then, it decides to make a sequential scan. But i am still not sure about the efficiency of this decision. Huh, so what was the EXPLAIN ANALYZE of the query with BETWEEN? -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) Syntax error: function hell() needs an argument. Please choose what hell you want to involve. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) ---(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
Re: [HACKERS] Optimizer bug??
Hi all, give us the result of these queries: SELECT COUNT(*) FROM articletbl; 268726 records, it takes 34169 ms. to compute this SELECT COUNT(*) AS c FROM articletbl WHERE mydate BETWEEN '2004-04-24' AND '2004-05-24'; 18982 records, it takes 34249 ms. to compute this. SELECT COUNT(*) AS c FROM articletbl WHERE mydate = '2004-04-24'; 850 records, it takes only 40 ms. to compute this. It is evident that there is a problem here! Am I wrong?? Regards Ismail Kizir ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Fw: [HACKERS] Fixing the Turkish problem
- Original Message - From: Ismail Kizir [EMAIL PROTECTED] To: Tom Lane [EMAIL PROTECTED] Sent: Friday, May 07, 2004 2:22 AM Subject: Re: [HACKERS] Fixing the Turkish problem Tom, Thank you very much for turkish locale fix. I think, that simple approach will fix the problem. And libpgport (src/port/) may be a good place to put function declarations. I am sure that you can make better decisions than me on that subject. Devrim wrote about a bug in glibc ... Do you know anything about it? Sometimes, I encounter strange behaviors with php(with unicode support) also. When I open a php generated page(utf-8 encoded source code), php interpreter gives Syntax error . And when i refresh the same page with F5, it works correctly. This may be a proof of that bug. Regards Ismail Kizir ---(end of broadcast)--- TIP 8: explain analyze is your friend
