[PERFORM] Variation between query runtimes
Hi,I have the following quering plans:Seq Scan on ind_uni_100 (cost=0.00..27242.00 rows=100 width=104) (actual time=0.272..2444.667 rows=100 loops=1)Total runtime: 4229.449 ms andBitmap Heap Scan on ind_uni_100 (cost=314.00..18181.00 rows=5 width=104) (actual time=74.106..585.368 rows=49758 loops=1) Recheck Cond: (b = 1) - Bitmap Index Scan on index_b_ind_uni_100 (cost= 0.00..314.00 rows=5 width=0) (actual time=61.814..61.814 rows=49758 loops=1) Index Cond: (b = 1)Total runtime: 638.787 msfrom pg_stast_get_blocks_fetched i can see that both queries need almost the same number of disk fetches which is quite reasonable ( the index is unclustered). But as you can see there is a great variation between query runtimes.Can someone explain this differnce?Thanks!
[PERFORM] function not called if part of aggregate
My application has a function, call it foo(), that requires initialization from a table of about 800 values. Rather than build these values into the C code, it seemed like a good idea to put them on a PG table and create a second function, call it foo_init(), which is called for each value, like this: select foo_init(value) from foo_init_table order by value_id; This works well, but it requires me to actually retrieve the function's value 800 times. So I thought I'd be clever: select count(1) from (select foo_init(value) from foo_init_table order by value_id) as foo; And indeed, it count() returns 800, as expected. But my function foo_init() never gets called! Apparently the optimizer figures out that foo_init() must return one value for each row, so it doesn't bother to actually call the function. db= explain select count(1) from (select foo_init(value) from foo_init_table order by db_no) as foo; query plan aggregate (cost=69.95..69.95 rows=1 width=0) - Subquery Scan foo (cost=0.00..67.93 rows=806 width=0) - Index Scan using foo_init_table_pkey on foo_init_table (cost=0.00..59.87 rows=806 width=30) This doesn't seem right to me -- how can the optimizer possibly know that a function doesn't have a side effect, as in my case? Functions could do all sorts of things, such as logging activity, filling in other tables, etc, etc. Am I missing something here? Thanks, Craig ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] function not called if part of aggregate
On Sun, Jun 11, 2006 at 10:18:20AM -0700, Craig A. James wrote: This works well, but it requires me to actually retrieve the function's value 800 times. Is this actually a problem? So I thought I'd be clever: select count(1) from (select foo_init(value) from foo_init_table order by value_id) as foo; Why not just count(foo_init(value))? /* Steinar */ -- Homepage: http://www.sesse.net/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] function not called if part of aggregate
Craig A. James [EMAIL PROTECTED] writes: select count(1) from (select foo_init(value) from foo_init_table order by value_id) as foo; And indeed, it count() returns 800, as expected. But my function foo_init() never gets called! Really? With the ORDER BY in there, it does get called, in my experiments. What PG version is this exactly? However, the short answer to your question is that PG does not guarantee to evaluate parts of the query not needed to determine the result. You could do something like select count(x) from (select foo_init(value) as x from foo_init_table order by value_id) as foo; to ensure that foo_init() must be evaluated. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] function not called if part of aggregate
On Sun, Jun 11, 2006 at 10:18:20AM -0700, Craig A. James wrote: This doesn't seem right to me -- how can the optimizer possibly know that a function doesn't have a side effect, as in my case? Functions could do all sorts of things, such as logging activity, filling in other tables, etc, etc. Am I missing something here? Read about function stability in the docs. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] function not called if part of aggregate
Craig A. James [EMAIL PROTECTED] writes: This doesn't seem right to me -- how can the optimizer possibly know that a function doesn't have a side effect, as in my case? Functions could do all sorts of things, such as logging activity, filling in other tables, etc, etc. The optimizer can know this if the user tells it so by marking the function IMMUTABLE. If the function is marked VOLATILE then the optimizer can know it might have side effects. However that's not enough to explain what you've shown. How about you show the actual query and actual plan you're working with? The plan you've shown can't result from the query you sent. -- greg ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] scaling up postgres
On Sun, Jun 11, 2006 at 11:42:20PM +0200, Mario Splivalo wrote: Could you point out to some more detailed reading on why Xeons are poorer choice than Opterons when used with PostgreSQL? There are lots of theories, none conclusive, but the benchmarks certainly point that way consistently. Read the list archives for the details. /* Steinar */ -- Homepage: http://www.sesse.net/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] scaling up postgres
On Sat, 2006-06-03 at 11:43 +0200, Steinar H. Gunderson wrote: On Sat, Jun 03, 2006 at 10:31:03AM +0100, [EMAIL PROTECTED] wrote: I do have 2 identical beasts (4G - biproc Xeon 3.2 - 2 Gig NIC) One beast will be apache, and the other will be postgres. I'm using httperf/autobench for measurments and the best result I can get is that my system can handle a trafiic of almost 1600 New con/sec. What version of PostgreSQL? (8.1 is better than 8.0 is much better than 7.4.) Have you remembered to turn HT off? Have you considered Opterons instead of Xeons? (The Xeons generally scale bad with PostgreSQL.) What kind of queries Could you point out to some more detailed reading on why Xeons are poorer choice than Opterons when used with PostgreSQL? Mario ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] scaling up postgres
Mario Splivalo wrote: On Sat, 2006-06-03 at 11:43 +0200, Steinar H. Gunderson wrote: On Sat, Jun 03, 2006 at 10:31:03AM +0100, [EMAIL PROTECTED] wrote: I do have 2 identical beasts (4G - biproc Xeon 3.2 - 2 Gig NIC) One beast will be apache, and the other will be postgres. I'm using httperf/autobench for measurments and the best result I can get is that my system can handle a trafiic of almost 1600 New con/sec. What version of PostgreSQL? (8.1 is better than 8.0 is much better than 7.4.) Have you remembered to turn HT off? Have you considered Opterons instead of Xeons? (The Xeons generally scale bad with PostgreSQL.) What kind of queries Could you point out to some more detailed reading on why Xeons are poorer choice than Opterons when used with PostgreSQL? It isn't just PostgreSQL. It is any database. Opterons can move memory and whole lot faster then Xeons. Joshua D. Drake Mario ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Variation between query runtimes
John Top-k apad [EMAIL PROTECTED] wrote from pg_stast_get_blocks_fetched i can see that both queries need almost the same number of disk fetches which is quite reasonable ( the index is unclustered). But as you can see there is a great variation between query runtimes.Cansomeone explain this differnce? Can you give a self-contained example (including what you did to clear the file system cache (maybe unmount?) to *not* let the 2nd query to use the file content from the 1st query)? Regards, Qingqing ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] pgsql_tmp and postgres settings
Where is the pgsql_tmp folder present ?. i am unable to see it in the data directory of postgresql. ~gourish On 6/9/06, Jim C. Nasby [EMAIL PROTECTED] wrote: On Fri, Jun 09, 2006 at 02:23:04PM +0200, Domenico - Sal. F.lli Riva wrote: Hello, During insert or update, potgresql write in pgsql_tmp directory and so performance are very poor.pgsql_tmp is used if a query runs out of work_mem, so you can tryincreasing that. My configuration is: Work mem10240 Effective_cache_size3You're off by a factor of 10. Shared buffers9000I'd suggest bumping that up to at least 3. Postgresql (RPM from official website) 8.1.0 You should upgrade to 8.1.4. There's a number of data loss bugs waitingto bite you.--Jim C. Nasby, Sr. Engineering Consultant[EMAIL PROTECTED]Pervasive Software http://pervasive.comwork: 512-231-6117vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461---(end of broadcast)--- TIP 4: Have you searched our list archives?http://archives.postgresql.org-- Best,Gourish Singbal
