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.

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/

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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




---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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/...functions.html

See "FOUND_ROWS()"

--
Postgresql & php tutorials
http://www.designmagick.com/

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Chris <dmagick@gmail.com> writes:
> Their docs explain it:
> http://dev.mysql.com/doc/refman/4.1/...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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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/...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





---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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/...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/

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

* Chris <dmagick@gmail.com> [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

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

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/...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



---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly