From G.De_Leeuw@eurofer.be Sun Feb 6 13:21:56 2011 Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 31EE41337B49 for ; Sun, 6 Feb 2011 09:21:56 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 19844-02 for ; Sun, 6 Feb 2011 13:21:50 +0000 (UTC) X-Greylist: delayed 00:05:56.894249 by SQLgrey-1.7.6 Received: from brublucs01.mactelecom.net (brublucs01.mactelecom.net [217.64.240.242]) by mail.postgresql.org (Postfix) with ESMTP id CA99C1336B42 for ; Sun, 6 Feb 2011 09:21:48 -0400 (AST) Received: by brublucs01.mactelecom.net (Postfix, from userid 500) id 09574F6477; Sun, 6 Feb 2011 13:15:49 +0000 (UTC) Received: from smtp.eurofer.be (217.64.254.218.mactelecom.net [217.64.254.218]) by brublucs01.mactelecom.net (Postfix) with ESMTP id 9B57EF6472 for ; Sun, 6 Feb 2011 13:15:48 +0000 (UTC) Received: from [192.168.5.10] by smtp.eurofer.be with esmtpsa (TLS1.0:DHE_RSA_AES_256_CBC_SHA1:32) (Exim 4.69) (envelope-from ) id 1Pm4Sv-0000Mk-Of for pgsql-admin@postgresql.org; Sun, 06 Feb 2011 14:15:49 +0100 From: Guy Deleeuw To: "pgsql-admin@postgresql.org" Content-Type: multipart/alternative; boundary="=-CmoDIiFecGk3V0SrQ6e5" Date: Sun, 06 Feb 2011 14:15:44 +0100 Message-ID: <1296998144.2024.14.camel@pc-1000> Mime-Version: 1.0 X-Mailer: Evolution 2.28.3 X-Spam_score: -3.7 X-SA-Exim-Connect-IP: 192.168.5.10 X-SA-Exim-Mail-From: G.De_Leeuw@eurofer.be Subject: How to X-SA-Exim-Scanned: Yes (on smtp.eurofer.be) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.899 tagged_above=-10 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201102/24 X-Sequence-Number: 36663 --=-CmoDIiFecGk3V0SrQ6e5 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: 7bit Hello I have a strange behaviour : time psql -d db_cathline -c "SELECT * FROM v_stock_status;" real 0m5.780s user 0m0.090s sys 0m0.020s time psql -d db_cathline -c "SELECT * FROM v_stock_status WHERE remain_qty != 0;" real 0m56.075s user 0m0.070s sys 0m0.030s Why with the clause WHERE the time are 10x greater than ? How can I work around this ? # select version(); version ------------------------------------------------------------------------------------------------------------- PostgreSQL 8.4.7 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.4.real (Ubuntu 4.4.3-4ubuntu5) 4.4.3, 64-bit (1 ligne) Regards Guy --=-CmoDIiFecGk3V0SrQ6e5 Content-Type: text/html; charset="utf-8" Content-Transfer-Encoding: 7bit Hello

I have a strange behaviour :
time psql -d db_cathline -c "SELECT * FROM v_stock_status;"

real 0m5.780s
user 0m0.090s
sys 0m0.020s

time psql -d db_cathline -c "SELECT * FROM v_stock_status WHERE remain_qty != 0;"

real 0m56.075s
user 0m0.070s
sys 0m0.030s

Why with the clause WHERE the time are 10x greater than ? How can I work around this ?

# select version();
                                                   version                                                  
-------------------------------------------------------------------------------------------------------------
PostgreSQL 8.4.7 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.4.real (Ubuntu 4.4.3-4ubuntu5) 4.4.3, 64-bit
(1 ligne)

Regards
Guy

--=-CmoDIiFecGk3V0SrQ6e5-- From Kevin.Grittner@wicourts.gov Sun Feb 6 13:40:52 2011 Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 006B51337B49 for ; Sun, 6 Feb 2011 09:40:52 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 23941-09 for ; Sun, 6 Feb 2011 13:40:46 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from gw.wicourts.gov (gwmta.wicourts.gov [165.219.244.99]) by mail.postgresql.org (Postfix) with ESMTP id 9C3B81336B42 for ; Sun, 6 Feb 2011 09:40:44 -0400 (AST) Received: from Courts-MTA by gw.wicourts.gov with Novell_GroupWise; Sun, 06 Feb 2011 07:40:44 -0600 Message-Id: <4D4E5076020000250003A4B6@gw.wicourts.gov> X-Mailer: Novell GroupWise Internet Agent 8.0.1 Date: Sun, 06 Feb 2011 07:40:37 -0600 From: "Kevin Grittner" To: , Subject: Re: How to Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit Content-Disposition: inline X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-4.21 tagged_above=-10 required=5 tests=BAYES_00=-1.9, RCVD_IN_DNSWL_MED=-2.3, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201102/25 X-Sequence-Number: 36664 Guy Deleeuw wrote: > Why with the clause WHERE the time are 10x greater than ? How can I > work around this ? Nobody can answer that without a bit more detail. Could you show an EXPLAIN ANALYZE of both queries and the definition of v_stock_status? I that is a view, then we would need definitions of each table. If you provide that and the contents of postgresql.conf with all comments stripped out, we can probably offer some suggestions. Well, a description of your hardware wouldn't hurt, particularly in terms of how much RAM you have and what your disk subsystem is like. -Kevin From Kevin.Grittner@wicourts.gov Sun Feb 6 16:31:15 2011 Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id E5C691337B83 for ; Sun, 6 Feb 2011 12:31:15 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 72132-07 for ; Sun, 6 Feb 2011 16:31:08 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from gw.wicourts.gov (gwmta.wicourts.gov [165.219.244.99]) by mail.postgresql.org (Postfix) with ESMTP id 4C7B11336B42 for ; Sun, 6 Feb 2011 12:31:08 -0400 (AST) Received: from Courts-MTA by gw.wicourts.gov with Novell_GroupWise; Sun, 06 Feb 2011 10:31:07 -0600 Message-Id: <4D4E7861020000250003A4C9@gw.wicourts.gov> X-Mailer: Novell GroupWise Internet Agent 8.0.1 Date: Sun, 06 Feb 2011 10:30:57 -0600 From: "Kevin Grittner" To: Cc: Subject: Re: How to Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit Content-Disposition: inline X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-4.21 tagged_above=-10 required=5 tests=BAYES_00=-1.9, RCVD_IN_DNSWL_MED=-2.3, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201102/26 X-Sequence-Number: 36665 Guy Deleeuw wrote: My hardware : lapto hp envy I7 processor with a ssd disk > Memory : > total used free shared buffers cached > Mem: 8129356 2866516 5262840 0 387172 948132 > -/+ buffers/cache: 1531212 6598144 > Swap: 15625208 0 15625208 It looks like everything you're running, including caching the entire database, or at least the active part of it, fits in less than 3 GB on your 8 GB machine. > shared_buffers = 24MB Given your hardware, I would bump that to 2GB or so. I would also set effective_cache_size to 7GB. I would set work_mem to at *least* 50MB; if you're only using a few connections, you could easily bump that to 100MB to 200MB. Fully cached, especially if it's all on PostgreSQL shared memory, seq_page_cost and random_page_cost both to 0.05. Between the SSD and having all data in shared memory, you might want to go even lower. There is some other general tuning to be done, but the above will make the most difference for read-only queries, which is what you're asking about. [nested views, using subqueries] You should probably look at reqorking the v_stock view to use joins instead of subqueries -- they often optimize much better. I didn't spend a lot of time with the EXPLAIN ANALYZE output, because if you configure PostgreSQL to get costing factors more in line with actual costs for that hardware, you're likely to see totally different plans. And I can pretty much guarantee that if you tune the costs *and* rework that view to use JOINs, you'll get completely different plans which will perform *much* better. I hope this helps. -Kevin From G.De_Leeuw@eurofer.be Mon Feb 7 10:47:16 2011 Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 321781337B34 for ; Mon, 7 Feb 2011 06:47:16 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 47574-07 for ; Mon, 7 Feb 2011 10:47:09 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from brublucs01.mactelecom.net (brublucs01.mactelecom.net [217.64.240.242]) by mail.postgresql.org (Postfix) with ESMTP id EEF591336971 for ; Mon, 7 Feb 2011 06:47:08 -0400 (AST) Received: by brublucs01.mactelecom.net (Postfix, from userid 500) id 25CA9F6500; Mon, 7 Feb 2011 10:47:06 +0000 (UTC) Received: from smtp.eurofer.be (217.64.254.218.mactelecom.net [217.64.254.218]) by brublucs01.mactelecom.net (Postfix) with ESMTP id 775EFF64FB; Mon, 7 Feb 2011 10:47:05 +0000 (UTC) Received: from pc-1000.eurofer.be ([192.168.3.167]) by smtp.eurofer.be with esmtpsa (TLS1.0:DHE_RSA_AES_256_CBC_SHA1:32) (Exim 4.69) (envelope-from ) id 1PmOcU-0002d4-PM; Mon, 07 Feb 2011 11:47:06 +0100 From: Guy Deleeuw To: Kevin Grittner Cc: pgsql-admin@postgresql.org In-Reply-To: <4D4E7861020000250003A4C9@gw.wicourts.gov> References: <4D4E7861020000250003A4C9@gw.wicourts.gov> Content-Type: text/plain; charset="UTF-8" Date: Mon, 07 Feb 2011 11:46:55 +0100 Message-ID: <1297075615.1961.3.camel@pc-1000> Mime-Version: 1.0 X-Mailer: Evolution 2.28.3 Content-Transfer-Encoding: 7bit X-Spam_score: -4.4 X-SA-Exim-Connect-IP: 192.168.3.167 X-SA-Exim-Mail-From: G.De_Leeuw@eurofer.be Subject: Re: How to X-SA-Exim-Scanned: Yes (on smtp.eurofer.be) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9 X-Spam-Level: X-Archive-Number: 201102/29 X-Sequence-Number: 36668 Hello Kevin, > > You should probably look at reqorking the v_stock view to use joins > instead of subqueries -- they often optimize much better. This is the problem effectively. For creating this view I use a query tools that generate the code, not a good idea. I update and work now :-) Best regards Guy From nicolas.thauvin@dalibo.com Mon Feb 7 18:06:53 2011 Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 8176D1337B99 for ; Mon, 7 Feb 2011 14:06:53 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 32023-03-5 for ; Mon, 7 Feb 2011 18:06:46 +0000 (UTC) X-Greylist: delayed 00:09:55.784264 by SQLgrey-1.7.6 Received: from mimolette.dalibo.net (unknown [212.85.154.222]) by mail.postgresql.org (Postfix) with ESMTP id 6537C1337BC3 for ; Mon, 7 Feb 2011 14:06:45 -0400 (AST) Received: from [192.168.0.12] (vbo91-5-88-186-16-24.fbx.proxad.net [88.186.16.24]) by mimolette.dalibo.net (Postfix) with ESMTPSA id 2A45BD827E; Mon, 7 Feb 2011 18:56:48 +0100 (CET) Message-ID: <4D503259.2000905@dalibo.com> Date: Mon, 07 Feb 2011 18:56:41 +0100 From: Nicolas Thauvin User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US; rv:1.9.1.16) Gecko/20101226 Icedove/3.0.11 MIME-Version: 1.0 To: Kevin Grittner CC: G.De_Leeuw@eurofer.be, pgsql-admin@postgresql.org Subject: Re: How to References: <4D4E7861020000250003A4C9@gw.wicourts.gov> In-Reply-To: <4D4E7861020000250003A4C9@gw.wicourts.gov> X-Enigmail-Version: 1.0.1 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.107 tagged_above=-10 required=5 tests=BAYES_00=-1.9, RDNS_NONE=0.793 X-Spam-Level: X-Archive-Number: 201102/34 X-Sequence-Number: 36673 -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 06/02/2011 17:30, Kevin Grittner wrote: > Guy Deleeuw wrote: > > My hardware : lapto hp envy I7 processor with a ssd disk > >> Memory : >> total used free shared buffers cached >> Mem: 8129356 2866516 5262840 0 387172 948132 >> -/+ buffers/cache: 1531212 6598144 >> Swap: 15625208 0 15625208 > > It looks like everything you're running, including caching the entire > database, or at least the active part of it, fits in less than 3 GB > on your 8 GB machine. > >> shared_buffers = 24MB > > Given your hardware, I would bump that to 2GB or so. > > I would also set effective_cache_size to 7GB. Here I think it is too much because when the 2 GB of shared memory will be used, only 6 GB of RAM will be left and maybe not entirely used for file system cache. My way to find effective_cache_size value is to put it to 50% of the amount of ram, drop the cache after tuning, leaving the server live for a while and have a look at the output of free to find the final value. > I would set work_mem > to at *least* 50MB; if you're only using a few connections, you could > easily bump that to 100MB to 200MB. Fully cached, especially if it's > all on PostgreSQL shared memory, seq_page_cost and random_page_cost > both to 0.05. Between the SSD and having all data in shared memory, > you might want to go even lower. There is some other general tuning > to be done, but the above will make the most difference for read-only > queries, which is what you're asking about. > > [nested views, using subqueries] > > You should probably look at reqorking the v_stock view to use joins > instead of subqueries -- they often optimize much better. > > I didn't spend a lot of time with the EXPLAIN ANALYZE output, because > if you configure PostgreSQL to get costing factors more in line with > actual costs for that hardware, you're likely to see totally > different plans. And I can pretty much guarantee that if you tune > the costs *and* rework that view to use JOINs, you'll get completely > different plans which will perform *much* better. > > I hope this helps. > > -Kevin > - -- Nicolas Thauvin DBA http://www.dalibo.com -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iQIcBAEBAgAGBQJNUDJZAAoJEDa2VZzWZKpN/L0P/i1/C7VRz1+vo1itkO3/VD9+ AMvErAsEh5TDfDM8/a4pZ6f6MVZzDxBf5+MB1kOTInUzhj48mS5+XHYJbdmzeRHy tNCCHhGGQ7M6l7W3cOCKh1tytumPuGzCw4rzFLi0/3IwBwi52AfnW+ibRSjXxGxU 2heAOwDc6amxTaocthuYna4lN+tgp23R3fENl6p+/2cGgN2WwXH1UytAUyH2OTFQ fxUZmyn7VUDnWb6u/vLsjVtAPz5Zq7Syc+VA0F886RFVguqau0tpHVI35Cqfu6rj Rd9LTC5+FkD1HiXGBWimEzJX6RfPaQ/Yq5TzVWqIZcUm7DIREEwZhfp+fiUeZzV+ XRK1aaTuREGNXaRp/ISsVrJI2Hp9lpX+qLrggWRNZpSJaSa03foS5kYBxA8Nnmbh M6qZIKTOC0gt0FiNdhONe8+kblEsLUMlEWS3RO6FXyHqCUsBi7kQOc0/07GLY52K m0lbWJ2QUEGc2uto1z1bReF+DK2+pxHVIZ0RF4yv+0OcM/ERVi/kn6pTHslhMOwP YFpAGaQ+3AflIvwq0WulqbnStuoMpDlCypic1s6sDshkkFMnQczDGhj0cI6fl+tP yPvuM14tpZV148LwZ1OGR7DoCi4cHbWuXcOuJ0R4hWDHUXtcmtsSetn4MBC8VS2O Ln484THmHEmLAmmu/rt6 =Rdav -----END PGP SIGNATURE----- From Kevin.Grittner@wicourts.gov Mon Feb 7 18:14:52 2011 Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 6FD841337B81 for ; Mon, 7 Feb 2011 14:14:52 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 85051-02 for ; Mon, 7 Feb 2011 18:14:45 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from gw.wicourts.gov (gwmta.wicourts.gov [165.219.244.99]) by mail.postgresql.org (Postfix) with ESMTP id 9C86F1337B34 for ; Mon, 7 Feb 2011 14:14:44 -0400 (AST) Received: from Courts-MTA by gw.wicourts.gov with Novell_GroupWise; Mon, 07 Feb 2011 12:14:43 -0600 Message-Id: <4D4FE22C020000250003A531@gw.wicourts.gov> X-Mailer: Novell GroupWise Internet Agent 8.0.1 Date: Mon, 07 Feb 2011 12:14:36 -0600 From: "Kevin Grittner" To: "Nicolas Thauvin" Cc: , Subject: Re: How to References: <4D4E7861020000250003A4C9@gw.wicourts.gov> <4D503259.2000905@dalibo.com> In-Reply-To: <4D503259.2000905@dalibo.com> Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit Content-Disposition: inline X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-4.21 tagged_above=-10 required=5 tests=BAYES_00=-1.9, RCVD_IN_DNSWL_MED=-2.3, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201102/35 X-Sequence-Number: 36674 Nicolas Thauvin wrote: > On 06/02/2011 17:30, Kevin Grittner wrote: >> Guy Deleeuw wrote: >> >> My hardware : lapto hp envy I7 processor with a ssd disk >> >>> Memory : >>> total used free shared buffers cached >>> 8129356 2866516 5262840 0 387172 948132 >>> -/+ buffers/cache: 1531212 6598144 >>> Swap: 15625208 0 15625208 >> >> It looks like everything you're running, including caching the >> entire database, or at least the active part of it, fits in less >> than 3 GB on your 8 GB machine. >> >>> shared_buffers = 24MB >> >> Given your hardware, I would bump that to 2GB or so. >> >> I would also set effective_cache_size to 7GB. > > Here I think it is too much because when the 2 GB of shared memory > will be used, only 6 GB of RAM will be left and maybe not entirely > used for file system cache. My way to find effective_cache_size > value is to put it to 50% of the amount of ram, drop the cache > after tuning, leaving the server live for a while and have a look > at the output of free to find the final value. Given that the usage of effective_cache_size seems to be an estimate of how much will be effectively cached if a single query pounds away at an index with random reads, adding shared buffers to the space available for OS caching seems reasonable to me, and doing so doesn't ever seem to have caused me problems. On the other hand, any size larger than your total database will be as effective as any other size larger than your database. ;-) For me, the database is usually bigger than RAM, so I just add shared_buffers to whatever "free" shows as cached. -Kevin