Thread: DDL question

Hello precious and brilliant minds,

I would like to know if there's a better way (syntactical or logical) to achieve a better performance for the scenario below:

Let's say we have any kind of query with a LIMIT and an OFFSET (used to limit/offset rows displayed on the web page). For example:

QUERY1:
SELECT col1, col2, col3 FROM tableA WHERE col1='qwerty' LIMIT 25 OFFSET 0;

Now I would like to run the same query WITHOUT LIMIT and OFFSET to obtain the total count of the rows:

QUERY2:
SELECT COUNT(*) INTO row_count FROM tableA WHERE col1='qwerty';

I use QUERY1 to display the actual data and QUERY2 to calculate the number of 'pages' to display web page pagination (like 1 - 2 - 3 --- 10).
To achive this I either run the two queries separately or combine the two queries by placing the second one as a subselect:

SELECT col1, col2, col3, (SELECT COUNT(*) FROM tableA WHERE col1='qwerty') AS row_count FROM tableA WHERE col1 = 'qwerty' LIMIT 25 OFFSET 0;

This way I'm able to collect both the data and the total row count. The problems:

1) This method requires me to type the 'same' query twice, one with LIMIT and OFFSET, the other one with COUNT(*) but without LIMIT and OFFSET.
2) In real cases, our queries are really complex joining 8 to 15 tables with several WHERE conditions. This double querying poses significant load on the server and is prone to typos.
3) I've also learned that COUNT(*) is slow due to sequential scans. In my scenario this is unavoidable since I require it, but there may be a trick to reduce its overall cost.

The only alternative solution I've come up with so far is to save row counts into the table itself using triggers. Is this reasonable or not?

I'm in search for a new method / syntax to combine these two queries and I welcome your suggestions.
Thanks in advance.

Volkan Varol
Ajansburada
http://www.ajansburada.com
Antalya, Turkey
T. +90 242 316 25 24
F. +90 242 316 25 52

On 22/01/2010 15:31, Volkan Varol wrote:
[snip]
> my scenario this is unavoidable since I require it, but there may be a
> trick to reduce its overall cost.
>
> The only alternative solution I've come up with so far is to save row
> counts into the table itself using triggers. Is this reasonable or not?

That approach has been suggested in the past, and seems a viable one.

> I'm in search for a new method / syntax to combine these two queries and
> I welcome your suggestions.

Would common table expressions be any use to you? -

  http://www.postgresql.org/docs/8.4/static/queries-with.html

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

On 22 Jan 2010, at 16:31, Volkan Varol wrote:

> Hello precious and brilliant minds,
>
> I would like to know if there's a better way (syntactical or logical) to achieve a better performance for the
scenariobelow: 
>
> Let's say we have any kind of query with a LIMIT and an OFFSET (used to limit/offset rows displayed on the web
page)..For example: 
>
> QUERY1:
> SELECT col1, col2, col3 FROM tableA WHERE col1='qwerty' LIMIT 25 OFFSET 0;
>
> Now I would like to run the same query WITHOUT LIMIT and OFFSET to obtain the total count of the rows:
>
> QUERY2:
> SELECT COUNT(*) INTO row_count FROM tableA WHERE col1='qwerty';
>
> I use QUERY1 to display the actual data and QUERY2 to calculate the number of 'pages' to display web page pagination
(like1 - 2 - 3 --- 10). 
> To achive this I either run the two queries separately or combine the two queries by placing the second one as a
subselect:

...

> I'm in search for a new method / syntax to combine these two queries and I welcome your suggestions.
> Thanks in advance.

There are a few options.

You can, as you suggested, pre-calculate your counts and store them in a table somewhere. This has an obvious drawback
ifyour query can vary with user input, storing counts for every possible combination of the query's parameters can
quicklybecome unwieldy. 

Another popular option is that works well if your results have a unique key (eg. their primary key) somewhere is to
queryonce to get all the key values and pass those along to the other pages in the pager. The query to fetch the actual
resultsper page often gets much simpler if you already know the primary keys. 

A variation on the above is to store the results in for example the session. You store all of them, but only the
informationyou need to display. As long as your result set isn't too large that works fine, but if it is too large you
shouldbe asking yourself how your users are going to find what they need among that many results. 

It's also possible to use a scrollable cursor, but that depends on your language being able to maintain the same
databasesession across the pages of your pager (eg. not PHP). To get the count you scroll the cursor to the end (which
takesabout as much time as a COUNT(*) query). 
With CTE's you could get an actual row number so that you know which number the one at the end is, but regardless that,
thequery result status also contains the number of records that you scrolled forward (just like the result status of an
insertcontains the number of rows inserted). 
IMHO that's the approach that matches best with what you're trying to achieve, but it's not always possible and some of
theother options may well outperform it. 

Alban Hertroys

--
Screwing up is the best way to attach something to the ceiling.


!DSPAM:737,4b59f10c10604404016430!