[PERFORM] same query in high number of times

2009-06-21 Thread Peter Alban
Hey folks ! Still kind of analyzing the situation , I realized that I do have a reasonably high shared_memory and effective_cache_size , though if the same query is being run in a number of times ~100-200 concurrent connection it is not being cached . Should PG realize that if the table data is

Re: [PERFORM] same query in high number of times

2009-06-21 Thread Justin Graf
With out knowing how much memory for each of those settings and how much work_mem for each connection its kinda hard to tell what is going. Also need version for PG, OS, how big the tables are, Also would be nice to see the query itself with explain and analyze PG does not cache the results

Re: [PERFORM] same query in high number of times

2009-06-21 Thread Robert Haas
On Sun, Jun 21, 2009 at 6:54 AM, Peter Albanpeter.alb...@gmail.com wrote: Should PG realize that if the table data is same should the query result set also be the same ? No. That's not so easy to implement as you might think. Saving the results of each previous query in case someone issues

Re: [PERFORM] same query in high number of times

2009-06-21 Thread Peter Alban
Hi, Here is the query : *duration: 2533.734 ms statement: * *SELECT news.url_text,news.title, comments.name, comments.createdate, comments.user_id, comments.comment FROM news, comments WHERE comments.cid= news.id AND comments.published='1' GROUP BY news.url_text,news.title comments.name,

Re: [PERFORM] same query in high number of times

2009-06-21 Thread Justin Graf
Peter Alban wrote: duration: 2533.734 ms statement: SELECT news.url_text,news.title, http://comments.name comments.name, comments.createdate, comments.user_id, comments.comment FROM news, comments WHERE comments.cid=http://news.id news.id AND comments.published='1' GROUP BY

Re: [PERFORM] same query in high number of times

2009-06-21 Thread Grzegorz Jaśkiewicz
On Sun, Jun 21, 2009 at 9:01 PM, Justin Grafjus...@emproshunts.com wrote: work_mem = 51024    # min 64, size in KB Thats allot memory dedicated to work mem if you have 30 connections open this could eat up 1.5gigs pushing the data out of cache. I thought work memory is max

Re: [PERFORM] same query in high number of times

2009-06-21 Thread Justin Graf
Message from mailto:gryz...@gmail.com Grzegorz Jaśkiewicz gryz...@gmail.com at 06-21-2009 09:36:01 PM -- On Sun, Jun 21, 2009 at 9:01 PM, Justin grafjus...@emproshunts.com wrote: work_mem = 51024 # min 64, size in KB Thats allot memory dedicated to work mem if you have 30

Re: [PERFORM] same query in high number of times

2009-06-21 Thread Scott Marlowe
On Sun, Jun 21, 2009 at 12:28 PM, Peter Albanpeter.alb...@gmail.com wrote: Hi, Here is the query  : duration: 2533.734 ms  statement: SNIP  Limit  (cost=4313.54..4313.55 rows=3 width=595) (actual time=288.525..288.528 rows=3 loops=1) According to this query plan, your query is taking up

Re: [PERFORM] same query in high number of times

2009-06-21 Thread Justin Graf
Message from mailto:peter.alb...@gmail.com Peter Alban peter.alb...@gmail.com at 06-21-2009 10:59:49 PM -- On Sun, Jun 21, 2009 at 10:01 PM, Justin Graf mailto:jus...@emproshunts.com wrote: Peter Alban wrote: duration: 2533.734 ms statement:

Re: [PERFORM] same query in high number of times

2009-06-21 Thread Robert Haas
On Sun, Jun 21, 2009 at 4:59 PM, Peter Albanpeter.alb...@gmail.com wrote: On Sun, Jun 21, 2009 at 10:01 PM, Justin Graf jus...@emproshunts.com wrote: Peter Alban wrote: duration: 2533.734 ms  statement: SELECT news.url_text,news.title, comments.name, comments.createdate,