Thread: select count(*) and limit
Hi, sometimes I have queries with a LIMIT statement. Now I'd like to present the user the returned records and inform him how many records there are if there was no LIMIT statement. Is it possible to get all neccessary information with one query? This works: SELECT * FROM table LIMIT 20 SELECT count(*) FROM table But is it possible to have one query returning both, the records and the count? regards, Verena
On 5/18/06 6:51 AM, "Verena Ruff" <lists@triosolutions.at> wrote: > Hi, > > sometimes I have queries with a LIMIT statement. Now I'd like to present > the user the returned records and inform him how many records there are > if there was no LIMIT statement. Is it possible to get all neccessary > information with one query? > This works: > SELECT * FROM table LIMIT 20 > SELECT count(*) FROM table > But is it possible to have one query returning both, the records and the > count? Verena I think the answer is "no", not when using "LIMIT". However, For the count part, a trick to speed things up is to use the output from EXPLAIN to approximate the number of rows. If the table has been vacuumed on a regular basis, the results are often pretty close to those returned by count(*). An alternative to using the LIMIT clause is to use a cursor, but the ability to do so depends on the environment in which you are working. In a web environment, cursors are not useful given the stateless nature of the web interface. Cursors are explained in the Docs. Sean
El día Thu, 18 May 2006 12:51:10 +0200 Verena Ruff <lists@triosolutions.at> escribió: > sometimes I have queries with a LIMIT statement. Now I'd like to present > the user the returned records and inform him how many records there are > if there was no LIMIT statement. Is it possible to get all neccessary > information with one query? > This works: > SELECT * FROM table LIMIT 20 > SELECT count(*) FROM table > But is it possible to have one query returning both, the records and the > count? A surely _INEFFICIENT_ way of doing it: SELECT t.*,c.count FROM table AS t FULL OUTER JOIN (SELECT count(*) FROM table) AS c ON true LIMIT 20; This will add a 'count' column at the end of each row (with the same value for all). But I do not get the point of the query. Regards, -- Oscar
Oscar Rodriguez Fonseca schrieb: > But I do not get the point of the query. > the reason why I'm asking is that I have some rather complex queries and I'd like to present the result in a paged way. I need to get the 10 records I'd like to present the user and I need to know how many records there are to calculate how many pages are needed and to create the neccessary links. The real query isn't as simple as the shown example, there are a few joins making it quite complex and slow. So I'm looking for a way to do this with one query and saving some time. Regards, Verena
Sean Davis schrieb: > > On 5/18/06 6:51 AM, "Verena Ruff" <lists@triosolutions.at> wrote: > > >> Hi, >> >> sometimes I have queries with a LIMIT statement. Now I'd like to present >> the user the returned records and inform him how many records there are >> if there was no LIMIT statement. Is it possible to get all neccessary >> information with one query? >> This works: >> SELECT * FROM table LIMIT 20 >> SELECT count(*) FROM table >> But is it possible to have one query returning both, the records and the >> count? >> > > Verena > > I think the answer is "no", not when using "LIMIT". However, For the count > part, a trick to speed things up is to use the output from EXPLAIN to > approximate the number of rows. If the table has been vacuumed on a regular > basis, the results are often pretty close to those returned by count(*). My chosen example was to simple, sorry for that. The real query isn't just from one table, it contains a few joins, so I guess this trick won't work here. > An > alternative to using the LIMIT clause is to use a cursor, but the ability to > do so depends on the environment in which you are working. In a web > environment, cursors are not useful given the stateless nature of the web > interface. Cursors are explained in the Docs. > The queries are for a webpage, so coursers won't be usefull. Regards, Verena
On 5/18/06 8:22 AM, "Verena Ruff" <lists@triosolutions.at> wrote: > Sean Davis schrieb: >> >> On 5/18/06 6:51 AM, "Verena Ruff" <lists@triosolutions.at> wrote: >> >> >>> Hi, >>> >>> sometimes I have queries with a LIMIT statement. Now I'd like to present >>> the user the returned records and inform him how many records there are >>> if there was no LIMIT statement. Is it possible to get all neccessary >>> information with one query? >>> This works: >>> SELECT * FROM table LIMIT 20 >>> SELECT count(*) FROM table >>> But is it possible to have one query returning both, the records and the >>> count? >>> >> >> Verena >> >> I think the answer is "no", not when using "LIMIT". However, For the count >> part, a trick to speed things up is to use the output from EXPLAIN to >> approximate the number of rows. If the table has been vacuumed on a regular >> basis, the results are often pretty close to those returned by count(*). > My chosen example was to simple, sorry for that. The real query isn't > just from one table, it contains a few joins, so I guess this trick > won't work here. It should still work just fine, again with the caveat that it is an approximation and depends on the statistics available. Try comparing the output a few times for your count(*) and using EXPLAIN. Sean
On May 18, 2006, at 8:22 AM, Verena Ruff wrote: >> An >> alternative to using the LIMIT clause is to use a cursor, but the >> ability to >> do so depends on the environment in which you are working. In a web >> environment, cursors are not useful given the stateless nature of >> the web >> interface. Cursors are explained in the Docs. >> > The queries are for a webpage, so coursers won't be usefull. A cursor still might be useful, but it would only be used for the current request. You could run the query once and fetch the rows you want to display from the cursor. Then scan to the end of the cursor to find out how many rows it has. The MOVE command does this and returns the number of rows. You would have to test it, but my guess is this would be faster than executing the same query twice for the two results you are looking for. Alternatively, you might skip calculating the true count unless the user clicks on a separate link. This option could show X rows from the end of the result set and the count(*) result. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL
John DeSoi schrieb: > A cursor still might be useful, but it would only be used for the > current request. You could run the query once and fetch the rows you > want to display from the cursor. Then scan to the end of the cursor to > find out how many rows it has. The MOVE command does this and returns > the number of rows. thanks for this hint. I'll test it. Regards, Verena
El día Thu, 18 May 2006 14:18:07 +0200 Verena Ruff <lists@triosolutions.at> escribió: > Oscar Rodriguez Fonseca schrieb: > > But I do not get the point of the query. > > > the reason why I'm asking is that I have some rather complex queries and > I'd like to present the result in a paged way. I need to get the 10 > records I'd like to present the user and I need to know how many records > there are to calculate how many pages are needed and to create the > neccessary links. The real query isn't as simple as the shown example, > there are a few joins making it quite complex and slow. So I'm looking > for a way to do this with one query and saving some time. Another way of doing it can be using UNION ALL and using the first value as such. E.g: TABLE IN DB: number_times | user_name | last_login ---------------------------------------- 1 | Paul | 11-12-2005 4 | Mary | 08-11-2005 5 | Charles | 01-02-2005 [...] SELECT count(*) AS number_times,NULL AS user_name,NULL as last_login \ FROM table UNION ALL SELECT * FROM table LIMIT 2; This has the practical drawback that you need an integer column in your table to get the query working as expected and the design drawback that it is a little bit weird and somewhat non-standard way of querying. I cannot think of another way of doing it. My guess is that limiting the query results should imply two queries to get the number of possible rows but these are my firsts steps with RDBMS so I may be wrong. BTW, I had a similar problem and solved it storing the full result list in a temporal variable on the server but "session-wise" (when the user opens another unrelated page, the application frees the variable). This is possible in my case because my app won't have more than 10 clients at once and therefore it don't represent much memory overhead. Regards. -- Oscar
Oscar Rodriguez Fonseca schrieb: > TABLE IN DB: > > > number_times | user_name | last_login > ---------------------------------------- > 1 | Paul | 11-12-2005 > 4 | Mary | 08-11-2005 > 5 | Charles | 01-02-2005 > > [...] > > SELECT count(*) AS number_times,NULL AS user_name,NULL as last_login \ > FROM table UNION ALL SELECT * FROM table LIMIT 2; > > This has the practical drawback that you need an integer column in your > table to get the query working as expected and the design drawback that > it is a little bit weird and somewhat non-standard way of querying. > I think this is a little bit to weired. It should be possible for another person what I am doing without explaing such a lot. > BTW, I had a similar problem and solved it storing the full result list > in a temporal variable on the server but "session-wise" (when the user > opens another unrelated page, the application frees the variable). This > is possible in my case because my app won't have more than 10 clients > at once and therefore it don't represent much memory overhead This is possible if there are only little users and only a few hundred records. I have only few users, too, but some tenthousonds records and I don't think it would be a good idea storing them completly in the servers memory. Regards, Verena
Hi, Sean Davis schrieb: > It should still work just fine, again with the caveat that it is an > approximation and depends on the statistics available. Try comparing the > output a few times for your count(*) and using EXPLAIN. > sometimes the difference is to much to go this way. EXPLAIN extimates 71000 rows but there are 95000. 10% would have been accaptle. I did these tests on a freshly vacuumed database. regards, Verena
On 5/18/06 12:28 PM, "Verena Ruff" <lists@triosolutions.at> wrote: > Hi, > > Sean Davis schrieb: >> It should still work just fine, again with the caveat that it is an >> approximation and depends on the statistics available. Try comparing the >> output a few times for your count(*) and using EXPLAIN. >> > sometimes the difference is to much to go this way. EXPLAIN extimates > 71000 rows but there are 95000. 10% would have been accaptle. I did > these tests on a freshly vacuumed database. You can change the statistics that are collected, if you like. Again, there is stuff in the docs about doing that. Sean
Hi, Sean Davis schrieb: > You can change the statistics that are collected, if you like. Again, there > is stuff in the docs about doing that > I didn't know that. Thanks for this hint, I'll read about it. Regards, Verena