Re: [PERFORM] avoiding seqscan?
--On måndag, september 29, 2003 15.32.31 +0200 Gaetano Mendola [EMAIL PROTECTED] wrote: Are not absolutelly bad but sometimes that path that you choose is not the optimal, in postgres 7.4 use the explicit join will be less limitative for the planner. Regards Gaetano Mendola Ah, OK. True! In this case though, the sql questions are crafted with great care, since we have a lot of data in a few of the tables, other are almost empty, so we try to limit the amount of data as early as possible. Our experience says that we often do a better job than the planner, since we know which tables are fat. Hence, we have actually moved to exlicit joins in questions and sometimes gained speed. But, in the general case, implicit might be better, I guess. Regards, Palle ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] avoiding seqscan?
Palle Girgensohn wrote: Will that make a difference? From what I've seen, it does not make much difference, but I have seen queries speed up when rewritten explicit joins. I guess it depends on other things, but is it really so that the explicit joins are bad somehow? Do you have any pointers to documentation about it, if so? Thanks, Palle Are not absolutelly bad but sometimes that path that you choose is not the optimal, in postgres 7.4 use the explicit join will be less limitative for the planner. Regards Gaetano Mendola ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] avoiding seqscan?
[EMAIL PROTECTED] (Palle Girgensohn) writes: Will that make a difference? From what I've seen, it does not make much difference, but I have seen queries speed up when rewritten explicit joins. I guess it depends on other things, but is it really so that the explicit joins are bad somehow? Do you have any pointers to documentation about it, if so? The problem is that if you expressly specify the joins, the query optimizer can't choose its own paths. And while that may not be better at the moment, it is quite possible that when you upgrade to a newer version, those queries, if not join-specified, could immediately get faster. I would expect that the query that uses implicit joins will be clearer to read, which adds a little further merit to that direction. That goes along with the usual way that it is preferable to optimize things, namely that you should start by solving the problem as simply as you can, and only proceed to further optimization if that actually proves necessary. Optimization efforts commonly add complexity and make code more difficult to maintain; that's not the place to start if you don't even know the effort is necessary. -- (format nil [EMAIL PROTECTED] cbbrowne libertyrms.info) http://dev6.int.libertyrms.com/ Christopher Browne (416) 646 3304 x124 (land) ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] avoiding seqscan?
Palle Girgensohn wrote: uu=# explain analyze uu-# select lower(substr(p.last_name,1,1)) as letter, count(*) uu-# FROM course c join group_data gd on (c.active_group_id = gd.this_group_id) uu-# join person p on (gd.item_text = p.userid) uu-# join dyn_field_person dfp on (dfp.extern_item_id = 10 and dfp.giver=c.giver) uu-# join dyn_field_content_person dfcp on (dfp.id = dfcp.dyn_field_id and dfcp.userid=p.userid) uu-# left outer join participant pt on (pt.userid = p.userid and pt.course_id = 707) uu-# WHERE c.id = 707 uu-# group by 1 uu-# ; Why are you using this form of join ? When and if is not necessary use the implicit form. Regards Gaetano Mendola ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] avoiding seqscan?
Palle Girgensohn wrote: Will that make a difference? From what I've seen, it does not make much difference, but I have seen queries speed up when rewritten explicit joins. I guess it depends on other things, but is it really so that the explicit joins are bad somehow? Do you have any pointers to documentation about it, if so? Thanks, Palle Are not absolutelly bad but sometimes that path that you choose is not the optimal, in postgres 7.4 the think will be better. Regards Gaetano Mendola ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] avoiding seqscan?
Palle, I have a SQL statement that I cannot get to use the index. postgresql insists on using a seqscan and performance is very poor. set enable_seqscan = true boost performance drastically, as you can see below. Since seqscan is not always bad, I'd rather not turn it off completely, but rather get the planner to do the right thing here. Is there another way to do this, apart from setting enable_seqscan=false? In your postgresql.conf, try setting effective_cache_size to something like 50% of your system's RAM, and lovering random_page_cost to 2.0 or even 1.5. Then restart PostgreSQL and try your query again. What version, btw? -- 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
Re: [PERFORM] avoiding seqscan?
Hi, Indeed, setting random_page_cost does the trick. Thanks! It seems to make sense to set random_page_cost to this value. Are there any drawbacks? postgresql-7.3.4 postgresql.conf: tcpip_socket = true max_connections = 100 superuser_reserved_connections = 2 # Performance # shared_buffers = 12000 sort_mem = 8192 vacuum_mem = 32768 effective_cache_size = 64000 random_page_cost = 2 ... --On söndag, september 28, 2003 14.34.25 -0700 Josh Berkus [EMAIL PROTECTED] wrote: Palle, I have a SQL statement that I cannot get to use the index. postgresql insists on using a seqscan and performance is very poor. set enable_seqscan = true boost performance drastically, as you can see below. Since seqscan is not always bad, I'd rather not turn it off completely, but rather get the planner to do the right thing here. Is there another way to do this, apart from setting enable_seqscan=false? In your postgresql.conf, try setting effective_cache_size to something like 50% of your system's RAM, and lovering random_page_cost to 2.0 or even 1.5. Then restart PostgreSQL and try your query again. What version, btw? -- 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 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] avoiding seqscan?
Palle, Indeed, setting random_page_cost does the trick. Thanks! It seems to make sense to set random_page_cost to this value. Are there any drawbacks? Only if your server was heavily multi-tasking, and as a result had little RAM+CPU available. Then you'd want to raise the value again. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
