From marcosborges@mbsi.com.br Thu Dec 7 13:19:35 2006 Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 183769FA040 for ; Thu, 7 Dec 2006 09:19:35 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024) with ESMTP id 65931-05 for ; Thu, 7 Dec 2006 09:19:24 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from canada.nswebhost.com (canada.nswebhost.com [66.246.218.207]) by postgresql.org (Postfix) with ESMTP id 17A569F938C for ; Thu, 7 Dec 2006 09:19:28 -0400 (AST) Received: from c953c1b5.virtua.com.br ([201.83.193.181]:1126 helo=[192.168.0.85]) by canada.nswebhost.com with esmtpa (Exim 4.52) id 1GsJ9m-00049c-Aa for pgsql-performance@postgresql.org; Thu, 07 Dec 2006 08:19:25 -0500 Message-ID: <457814D3.1030001@mbsi.com.br> Date: Thu, 07 Dec 2006 11:19:15 -0200 From: Marcos Borges Reply-To: marcosborges@mbsi.com.br User-Agent: Thunderbird 1.5.0.8 (Windows/20061025) MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: SQL_CALC_FOUND_ROWS in POSTGRESQL / Some one can helpe me. Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-ClamAntiVirus-Scanner: This mail is clean X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: Primary Hostname - canada.nswebhost.com X-AntiAbuse: Original Domain - postgresql.org X-AntiAbuse: Originator/Caller UID/GID - [0 0] / [47 12] X-AntiAbuse: Sender Address Domain - mbsi.com.br X-Source: X-Source-Args: X-Source-Dir: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.096 tagged_above=0 required=5 tests=AWL, BAYES_50, HTML_30_40, HTML_MESSAGE, MIME_HTML_ONLY, UPPERCASE_25_50 X-Spam-Level: X-Archive-Number: 200612/174 X-Sequence-Number: 22230
07/12/2006 04:31
SQL_CALC_FOUND_ROWS in POSTGRESQL

In mysqln i m using the command SQL_CALC_FOUND_ROWS in follow sintax.
SELECT SQL_CALC_FOUND_ROWS name, email, tel FROM mytable WHERE name <> '' LIMIT 0, 10
to have the recorset data.
and
SELECT FOUND_ROWS();
to have the total of registers found.

I dont want to use the command count(*), because the performance will fall down, depending of the quantyt of tables and "joins".

The Data base postgresql have something similar ???


---------------------------------------------------------------------------------------------------

07/12/2006 04:31
SQL_CALC_FOUND_ROWS no POSTGRESQL
Dúvida NINJA no POSTGRESQL
No mysql utilizo o comando SQL_CALC_FOUND_ROWS na seguinte sintax
SELECT SQL_CALC_FOUND_ROWS nome, email, telefone FROM tabela WHERE nome <> '' LIMIT 0, 10
para obter o meu recordset
e
SELECT FOUND_ROWS();
para obter o total de resgitros que realmente existem em minha tabela condicionado pelo WHERE, sem ser limitado pelo LIMIT.

Não quero usar o count(*) pois o desempenho cai dependendo da quantidade de tabelas selecionadas e quantidade de registros.


O postgreSQL possui algo similar? Caso sim pode me informar qual e fornecer um exemplo.

From dmagick@gmail.com Mon Dec 11 03:33:34 2006 Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id C00819F9C35 for ; Sun, 10 Dec 2006 23:33:34 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-new, port 10024) with ESMTP id 47285-08 for ; Sun, 10 Dec 2006 23:33:25 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from wx-out-0506.google.com (wx-out-0506.google.com [66.249.82.231]) by postgresql.org (Postfix) with ESMTP id 7E9439F9386 for ; Sun, 10 Dec 2006 23:33:25 -0400 (AST) Received: by wx-out-0506.google.com with SMTP id i28so1578638wxd for ; Sun, 10 Dec 2006 19:33:24 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:user-agent:mime-version:to:cc:subject:references:in-reply-to:content-type:content-transfer-encoding; b=NdhoQ93OICBoTNiFvpM0bLHzMiEsUjh/QMg8Lv96PL3KbEeCn0eT9v9ORlvtpZGVRodDQSYBAbPiQQ2Q4THHBtCcoOFT7atYmT2AiQi5kz7w/BFb5RG7zRXmH69eiYt1MTFhj7DM4LTT76bcWa6qE2FaqjguURnvJDvWu0lqDFg= Received: by 10.70.68.11 with SMTP id q11mr11391654wxa.1165808004029; Sun, 10 Dec 2006 19:33:24 -0800 (PST) Received: from ?10.1.1.11? ( [203.217.18.65]) by mx.google.com with ESMTP id g9sm11137300wra.2006.12.10.19.33.22; Sun, 10 Dec 2006 19:33:23 -0800 (PST) Message-ID: <457CD182.7020004@gmail.com> Date: Mon, 11 Dec 2006 14:33:22 +1100 From: Chris User-Agent: Thunderbird 1.5.0.8 (Windows/20061025) MIME-Version: 1.0 To: marcosborges@mbsi.com.br CC: pgsql-performance@postgresql.org Subject: Re: SQL_CALC_FOUND_ROWS in POSTGRESQL / Some one can helpe References: <457814D3.1030001@mbsi.com.br> In-Reply-To: <457814D3.1030001@mbsi.com.br> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/200 X-Sequence-Number: 22256 Marcos Borges wrote: > 07/12/2006 04:31 > *SQL_CALC_FOUND_ROWS in POSTGRESQL* > > In mysqln i m using the command SQL_CALC_FOUND_ROWS in follow sintax. > SELECT SQL_CALC_FOUND_ROWS name, email, tel FROM mytable WHERE name <> > '' LIMIT 0, 10 > to have the recorset data. > and > SELECT FOUND_ROWS(); > to have the total of registers found. > > I dont want to use the command count(*), because the performance will > fall down, depending of the quantyt of tables and "joins". > > The Data base postgresql have something similar ??? Nope, you're out of luck sorry. That's a mysql-ism and I doubt postgres will ever include something similar. -- Postgresql & php tutorials http://www.designmagick.com/ From jd@commandprompt.com Mon Dec 11 04:02:49 2006 Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 580969FA2FC for ; Mon, 11 Dec 2006 00:02:49 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-new, port 10024) with ESMTP id 52270-03 for ; Mon, 11 Dec 2006 00:02:44 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from lists.commandprompt.com (host-130.commandprompt.net [207.173.203.130]) by postgresql.org (Postfix) with ESMTP id 3549E9FA248 for ; Mon, 11 Dec 2006 00:02:43 -0400 (AST) Received: from [192.168.1.95] (or-67-76-146-141.sta.embarqhsd.net [67.76.146.141]) (authenticated bits=0) by lists.commandprompt.com (8.13.7/8.13.6) with ESMTP id kBB42jlY010927 (version=TLSv1/SSLv3 cipher=RC4-MD5 bits=128 verify=NO); Sun, 10 Dec 2006 20:02:46 -0800 Subject: Re: SQL_CALC_FOUND_ROWS in POSTGRESQL / Some one can From: "Joshua D. Drake" To: Chris Cc: marcosborges@mbsi.com.br, pgsql-performance@postgresql.org In-Reply-To: <457CD182.7020004@gmail.com> References: <457814D3.1030001@mbsi.com.br> <457CD182.7020004@gmail.com> Content-Type: text/plain Organization: Command Prompt, Inc. Date: Sun, 10 Dec 2006 20:04:38 -0800 Message-Id: <1165809878.19970.2.camel@localhost.localdomain> Mime-Version: 1.0 X-Mailer: Evolution 2.8.1 Content-Transfer-Encoding: 7bit X-Virus-Scanned: ClamAV version 0.88.5, clamav-milter version 0.88.5 on projects.commandprompt.com X-Virus-Status: Clean X-Greylist: Sender succeded SMTP AUTH authentication, not delayed by milter-greylist-1.6 (lists.commandprompt.com [192.168.2.159]); Sun, 10 Dec 2006 20:02:47 -0800 (PST) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/202 X-Sequence-Number: 22258 On Mon, 2006-12-11 at 14:33 +1100, Chris wrote: > Marcos Borges wrote: > > 07/12/2006 04:31 > > *SQL_CALC_FOUND_ROWS in POSTGRESQL* > > > > In mysqln i m using the command SQL_CALC_FOUND_ROWS in follow sintax. > > SELECT SQL_CALC_FOUND_ROWS name, email, tel FROM mytable WHERE name <> > > '' LIMIT 0, 10 > > to have the recorset data. > > and > > SELECT FOUND_ROWS(); > > to have the total of registers found. > > > > I dont want to use the command count(*), because the performance will > > fall down, depending of the quantyt of tables and "joins". > > > > The Data base postgresql have something similar ??? > > Nope, you're out of luck sorry. That's a mysql-ism and I doubt postgres > will ever include something similar. Your language will have a similar binding. Something like pg_numrows. Sincerely, Joshua D. Drake > -- === 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/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate From dmagick@gmail.com Mon Dec 11 04:36:20 2006 Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id DB94D9FA249 for ; Mon, 11 Dec 2006 00:36:20 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-new, port 10024) with ESMTP id 57775-03 for ; Mon, 11 Dec 2006 00:36:15 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from wx-out-0506.google.com (wx-out-0506.google.com [66.249.82.236]) by postgresql.org (Postfix) with ESMTP id 97B369FA0EE for ; Mon, 11 Dec 2006 00:36:15 -0400 (AST) Received: by wx-out-0506.google.com with SMTP id i28so1592042wxd for ; Sun, 10 Dec 2006 20:36:14 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:user-agent:mime-version:to:cc:subject:references:in-reply-to:content-type:content-transfer-encoding; b=DBAUH86ie7jgEr2t9yksKG+lSFr7SoDVCcE0dJZR3rY8+eGMYoWggnEwuBJ1UpM0sS64plbqkYoM0R45Jl/pxegFKUR4AOa4TKZLMzdvE8mr/2I31HVreIIQGNZpbHLax5cQ9O+SNbaOr7Em1P/A4Ud2yz1ksDKefdtjYewwVnw= Received: by 10.70.40.1 with SMTP id n1mr4425700wxn.1165811774534; Sun, 10 Dec 2006 20:36:14 -0800 (PST) Received: from ?10.1.1.11? ( [203.217.18.65]) by mx.google.com with ESMTP id 6sm7660167wrh.2006.12.10.20.36.12; Sun, 10 Dec 2006 20:36:14 -0800 (PST) Message-ID: <457CE03C.1060707@gmail.com> Date: Mon, 11 Dec 2006 15:36:12 +1100 From: Chris User-Agent: Thunderbird 1.5.0.8 (Windows/20061025) MIME-Version: 1.0 To: "Joshua D. Drake" CC: marcosborges@mbsi.com.br, pgsql-performance@postgresql.org Subject: Re: SQL_CALC_FOUND_ROWS in POSTGRESQL / Some one can References: <457814D3.1030001@mbsi.com.br> <457CD182.7020004@gmail.com> <1165809878.19970.2.camel@localhost.localdomain> In-Reply-To: <1165809878.19970.2.camel@localhost.localdomain> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/203 X-Sequence-Number: 22259 Joshua D. Drake wrote: > On Mon, 2006-12-11 at 14:33 +1100, Chris wrote: >> Marcos Borges wrote: >>> 07/12/2006 04:31 >>> *SQL_CALC_FOUND_ROWS in POSTGRESQL* >>> >>> In mysqln i m using the command SQL_CALC_FOUND_ROWS in follow sintax. >>> SELECT SQL_CALC_FOUND_ROWS name, email, tel FROM mytable WHERE name <> >>> '' LIMIT 0, 10 >>> to have the recorset data. >>> and >>> SELECT FOUND_ROWS(); >>> to have the total of registers found. >>> >>> I dont want to use the command count(*), because the performance will >>> fall down, depending of the quantyt of tables and "joins". >>> >>> The Data base postgresql have something similar ??? >> Nope, you're out of luck sorry. That's a mysql-ism and I doubt postgres >> will ever include something similar. > > Your language will have a similar binding. Something like pg_numrows. I guess they are similar but also not really :) The SQL_CALC_FOUND_ROWS directive in mysql will run the same query but without the limit. It's the same as doing a select count(*) type query using the same clauses, but all in one query instead of two. It doesn't return any extra rows on top of the limit query so it's better than using pg_numrows which runs the whole query and returns it to php (in this example). Their docs explain it: http://dev.mysql.com/doc/refman/4.1/en/information-functions.html See "FOUND_ROWS()" -- Postgresql & php tutorials http://www.designmagick.com/ From tgl@sss.pgh.pa.us Mon Dec 11 04:57:25 2006 Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 666729FA0EE for ; Mon, 11 Dec 2006 00:57:25 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-new, port 10024) with ESMTP id 59111-07 for ; Mon, 11 Dec 2006 00:57:20 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 095E69F9392 for ; Mon, 11 Dec 2006 00:57:20 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id kBB4vI5O026693; Sun, 10 Dec 2006 23:57:18 -0500 (EST) To: Chris cc: "Joshua D. Drake" , marcosborges@mbsi.com.br, pgsql-performance@postgresql.org Subject: Re: SQL_CALC_FOUND_ROWS in POSTGRESQL / Some one can In-reply-to: <457CE03C.1060707@gmail.com> References: <457814D3.1030001@mbsi.com.br> <457CD182.7020004@gmail.com> <1165809878.19970.2.camel@localhost.localdomain> <457CE03C.1060707@gmail.com> Comments: In-reply-to Chris message dated "Mon, 11 Dec 2006 15:36:12 +1100" Date: Sun, 10 Dec 2006 23:57:18 -0500 Message-ID: <26692.1165813038@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/204 X-Sequence-Number: 22260 Chris writes: > Their docs explain it: > http://dev.mysql.com/doc/refman/4.1/en/information-functions.html > See "FOUND_ROWS()" Sounds like a pretty ugly crock ... The functionality as described is to let you fetch only the first N rows, and then still find out the total number of rows that could have been returned. You can do that in Postgres with a cursor: DECLARE c CURSOR FOR SELECT ... (no LIMIT here); FETCH n FROM c; MOVE FORWARD ALL IN c; -- then figure the sum of the number of rows fetched and the -- rows-moved count reported by MOVE regards, tom lane From markir@paradise.net.nz Mon Dec 11 05:50:24 2006 Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 19BCC9F9386 for ; Mon, 11 Dec 2006 01:50:24 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-new, port 10024) with ESMTP id 85357-05 for ; Mon, 11 Dec 2006 01:50:15 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from smtp5.clear.net.nz (smtp5.clear.net.nz [203.97.33.68]) by postgresql.org (Postfix) with ESMTP id D8B8E9F9357 for ; Mon, 11 Dec 2006 01:50:14 -0400 (AST) Received: from [192.168.1.11] (121-72-71-26.dsl.telstraclear.net [121.72.71.26]) by smtp5.clear.net.nz (CLEAR Net Mail) with ESMTP id <0JA300JETHJO7240@smtp5.clear.net.nz> for pgsql-performance@postgresql.org; Mon, 11 Dec 2006 18:50:13 +1300 (NZDT) Date: Mon, 11 Dec 2006 18:49:56 +1300 From: Mark Kirkwood Subject: Re: SQL_CALC_FOUND_ROWS in POSTGRESQL / Some one can In-reply-to: <457CE03C.1060707@gmail.com> To: Chris Cc: "Joshua D. Drake" , marcosborges@mbsi.com.br, pgsql-performance@postgresql.org Message-id: <457CF184.6020702@paradise.net.nz> MIME-version: 1.0 Content-type: text/plain; charset=ISO-8859-1; format=flowed Content-transfer-encoding: 7bit References: <457814D3.1030001@mbsi.com.br> <457CD182.7020004@gmail.com> <1165809878.19970.2.camel@localhost.localdomain> <457CE03C.1060707@gmail.com> User-Agent: Thunderbird 1.5.0.8 (X11/20061129) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/205 X-Sequence-Number: 22261 Chris wrote: > It's the same as doing a select count(*) type query using the same > clauses, but all in one query instead of two. > > It doesn't return any extra rows on top of the limit query so it's > better than using pg_numrows which runs the whole query and returns it > to php (in this example). > > > Their docs explain it: > > http://dev.mysql.com/doc/refman/4.1/en/information-functions.html > > See "FOUND_ROWS()" > Note that from the same page: "If you are using SELECT SQL_CALC_FOUND_ROWS, MySQL must calculate how many rows are in the full result set. However, this is faster than running the query again without LIMIT, because the result set need not be sent to the client." So it is not as cost-free as it would seem - the CALC step is essentially doing "SELECT count(*) FROM (your-query)" in addition to your-query-with-the-limit. I don't buy the "its cheap 'cause nothing is returned to the client" bit, because 'SELECT count(*) ...' returns only 1 tuple of 1 element to the client anyway. On the face of it, it *looks* like you save an extra set of parse, execute, construct (trivially small) resultset calls - but 'SELECT FOUND_ROWS()' involves that set of steps too, so I'm not entirely convinced that doing a 2nd 'SELECT count(*)...' is really any different in impact. Cheers Mark From dmagick@gmail.com Mon Dec 11 06:01:26 2006 Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id B9BD09F9357 for ; Mon, 11 Dec 2006 02:01:26 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-new, port 10024) with ESMTP id 89806-01 for ; Mon, 11 Dec 2006 02:01:14 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from wx-out-0506.google.com (wx-out-0506.google.com [66.249.82.228]) by postgresql.org (Postfix) with ESMTP id C736F9F9F46 for ; Mon, 11 Dec 2006 02:01:13 -0400 (AST) Received: by wx-out-0506.google.com with SMTP id i28so1608794wxd for ; Sun, 10 Dec 2006 22:01:12 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:user-agent:mime-version:to:cc:subject:references:in-reply-to:content-type:content-transfer-encoding; b=R1jqhsoVH7tg3d6kJwq/cNYG3rssCFy4ialixm+vfvQlI3ZIuQD6aYf7cmS4z+7psSF5LJBiGYInBQdd6uRi/r7DqQMoqPQCMbDPbCBReeQ5JKh+r9xvECZD1Hw+QglA87KbhvmqhwZlyKDs/j6RFgbMO+wAPgPUFMK9r9hSsWE= Received: by 10.70.113.13 with SMTP id l13mr11644116wxc.1165816872705; Sun, 10 Dec 2006 22:01:12 -0800 (PST) Received: from ?10.1.1.11? ( [203.217.18.65]) by mx.google.com with ESMTP id 11sm5283122wrl.2006.12.10.22.01.10; Sun, 10 Dec 2006 22:01:12 -0800 (PST) Message-ID: <457CF427.30507@gmail.com> Date: Mon, 11 Dec 2006 17:01:11 +1100 From: Chris User-Agent: Thunderbird 1.5.0.8 (Windows/20061025) MIME-Version: 1.0 To: Mark Kirkwood CC: pgsql-performance@postgresql.org Subject: Re: SQL_CALC_FOUND_ROWS in POSTGRESQL / Some one can References: <457814D3.1030001@mbsi.com.br> <457CD182.7020004@gmail.com> <1165809878.19970.2.camel@localhost.localdomain> <457CE03C.1060707@gmail.com> <457CF184.6020702@paradise.net.nz> In-Reply-To: <457CF184.6020702@paradise.net.nz> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/206 X-Sequence-Number: 22262 Mark Kirkwood wrote: > Chris wrote: > >> It's the same as doing a select count(*) type query using the same >> clauses, but all in one query instead of two. >> >> It doesn't return any extra rows on top of the limit query so it's >> better than using pg_numrows which runs the whole query and returns it >> to php (in this example). >> >> >> Their docs explain it: >> >> http://dev.mysql.com/doc/refman/4.1/en/information-functions.html >> >> See "FOUND_ROWS()" >> > > Note that from the same page: > > "If you are using SELECT SQL_CALC_FOUND_ROWS, MySQL must calculate how > many rows are in the full result set. However, this is faster than > running the query again without LIMIT, because the result set need not > be sent to the client." > > So it is not as cost-free as it would seem - the CALC step is > essentially doing "SELECT count(*) FROM (your-query)" in addition to > your-query-with-the-limit. > > I don't buy the "its cheap 'cause nothing is returned to the client" > bit, because 'SELECT count(*) ...' returns only 1 tuple of 1 element to > the client anyway. On the face of it, it *looks* like you save an extra > set of parse, execute, construct (trivially small) resultset calls - but > 'SELECT FOUND_ROWS()' involves that set of steps too, so I'm not > entirely convinced that doing a 2nd 'SELECT count(*)...' is really any > different in impact. Sorry - I created a bit of confusion here. It's not doing the count(*), it's doing the query again without the limit. ie: select SQL_CALC_FOUND_ROWS userid, username, password from users limit 10; will do: select userid, username, password from users limit 10; and calculate this: select userid, username, password from users; and tell you how many rows that will return (so you can call 'found_rows()'). the second one does do a lot more because it has to send the results across to the client program - whether the client uses that info or not doesn't matter. The OP didn't want to have to change to using two different queries: select count(*) from table; select * from table limit 10 offset 0; Josh's comment was to do the query again without the limit: select userid, username, password from users; and then use something like http://www.php.net/pg_numrows to work out the number of results the query would have returned.. but that would keep the dataset in memory and eventually with a large enough dataset cause a problem. -- Postgresql & php tutorials http://www.designmagick.com/ From andreas@kostyrka.org Mon Dec 11 06:38:49 2006 Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 956B79FA373 for ; Mon, 11 Dec 2006 02:38:49 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024) with ESMTP id 05265-03 for ; Mon, 11 Dec 2006 02:38:42 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from heaven.kostyrka.org (h1634.serverkompetenz.net [81.169.154.44]) by postgresql.org (Postfix) with ESMTP id B0E389F9F08 for ; Mon, 11 Dec 2006 02:38:41 -0400 (AST) Received: from localhost (localhost [127.0.0.1]) by heaven.kostyrka.org (Postfix) with ESMTP id 7FDA94FBE5; Mon, 11 Dec 2006 07:38:39 +0100 (CET) Received: from heaven.kostyrka.org ([127.0.0.1]) by localhost (heaven.kostyrka.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 08103-08; Mon, 11 Dec 2006 07:38:39 +0100 (CET) Received: from andi-lap (localhost [127.0.0.1]) by heaven.kostyrka.org (Postfix) with SMTP id 358754FBE4; Mon, 11 Dec 2006 07:38:38 +0100 (CET) Received: by andi-lap (sSMTP sendmail emulation); Mon, 11 Dec 2006 07:42:14 +0100 Date: Mon, 11 Dec 2006 07:42:14 +0100 From: Andreas Kostyrka To: Chris Cc: Mark Kirkwood , pgsql-performance@postgresql.org Subject: Re: SQL_CALC_FOUND_ROWS in POSTGRESQL / Some one can Message-ID: <20061211064214.GV2050@andi-lap.la.revver.com> References: <457814D3.1030001@mbsi.com.br> <457CD182.7020004@gmail.com> <1165809878.19970.2.camel@localhost.localdomain> <457CE03C.1060707@gmail.com> <457CF184.6020702@paradise.net.nz> <457CF427.30507@gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <457CF427.30507@gmail.com> User-Agent: mutt-ng/devel-r804 (Debian) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/207 X-Sequence-Number: 22263 * Chris [061211 07:01]: > select SQL_CALC_FOUND_ROWS userid, username, password from users limit 10; > > will do: > > select userid, username, password from users limit 10; > > and calculate this: > > select userid, username, password from users; > > and tell you how many rows that will return (so you can call 'found_rows()'). > > > the second one does do a lot more because it has to send the results across to the client program - whether the client uses that info or not doesn't matter. Not really. Sending the data to the client is usually (if you are not connected via some small-bandwidth connection) a trivial cost compared to calculating the number of rows. (Our tables involve 100Ms of rows, while the net connectivity is a private internal Gigabit net, returning the data seems never to be an issue. Reading it from the disc, selecting the rows are issues. Not sending the data.) Actually, if you think that sending the data is an issue, PG offers the more generic concept of cursors. Andreas From gnari@hive.is Mon Dec 11 09:39:30 2006 Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 844EA9FA304 for ; Mon, 11 Dec 2006 05:39:30 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-new, port 10024) with ESMTP id 45878-08-2 for ; Mon, 11 Dec 2006 05:39:25 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from mx1.ipf.is (mack.ipf.is [85.197.192.164]) by postgresql.org (Postfix) with ESMTP id 358BB9FA2E0 for ; Mon, 11 Dec 2006 05:39:24 -0400 (AST) Received: (qmail 11736 invoked by uid 1010); 11 Dec 2006 09:36:12 +0000 Received: from 88.149.106.19 by mack (envelope-from , uid 1008) with qmail-scanner-1.25-st-qms (clamdscan: 0.88.4/1639. spamassassin: 3.1.4. perlscan: 1.25-st-qms. Clear:RC:1(88.149.106.19):SA:0(-2.4/4.5):. Processed in 0.451636 secs); 11 Dec 2006 09:36:12 -0000 X-Antivirus-HIVE-Mail-From: gnari@hive.is via mack X-Antivirus-HIVE: 1.25-st-qms (Clear:RC:1(88.149.106.19):SA:0(-2.4/4.5):. Processed in 0.451636 secs Process 11729) Received: from dsl-149-106-19.hive.is (HELO ?192.168.1.33?) (88.149.106.19) by mx1.ipf.is with SMTP; 11 Dec 2006 09:36:11 +0000 Subject: Re: SQL_CALC_FOUND_ROWS in POSTGRESQL / Some one can From: Ragnar To: Chris Cc: Mark Kirkwood , pgsql-performance@postgresql.org In-Reply-To: <457CF427.30507@gmail.com> References: <457814D3.1030001@mbsi.com.br> <457CD182.7020004@gmail.com> <1165809878.19970.2.camel@localhost.localdomain> <457CE03C.1060707@gmail.com> <457CF184.6020702@paradise.net.nz> <457CF427.30507@gmail.com> Content-Type: text/plain; charset=ISO-8859-1 Date: Mon, 11 Dec 2006 09:39:32 +0000 Message-Id: <1165829972.6369.26.camel@localhost.localdomain> Mime-Version: 1.0 X-Mailer: Evolution 2.8.1 Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/213 X-Sequence-Number: 22269 On mán, 2006-12-11 at 17:01 +1100, Chris wrote: > Mark Kirkwood wrote: > > Chris wrote: > > > >> It's the same as doing a select count(*) type query using the same > >> clauses, but all in one query instead of two. > >> > >> It doesn't return any extra rows on top of the limit query so it's > >> better than using pg_numrows which runs the whole query and returns it > >> to php (in this example). > >> > >> > >> Their docs explain it: > >> > >> http://dev.mysql.com/doc/refman/4.1/en/information-functions.html > >> > >> See "FOUND_ROWS()" > >> > > > > Note that from the same page: > > > > "If you are using SELECT SQL_CALC_FOUND_ROWS, MySQL must calculate how > > many rows are in the full result set. However, this is faster than > > running the query again without LIMIT, because the result set need not > > be sent to the client." yes but not any faster than a select count(*) from (full query without LIMIT) so the only advantage to the SQL_CALC_FOUND_ROWS thingie is that instead of doing select count(*) from full-query select * from query-with-LIMIT which will do the query twice, but possibly with different optimisations, you would do a non-standard select SQL_CALC_FOUND_ROWS query-with-LIMIT select FOUND_ROWS() which will do one full query, without any LIMIT optimisation, but with the same number of round-trips, and same amount of data over the line. the only case where the second way may be more effective, is if no LIMIT optimisation can be made, and where the dataset is larger than file buffer space, so that there is no effect from caching. gnari