Skip site navigation (1) Skip section navigation (2)

Re: [PERFORM] Find original number of rows before applied LIMIT/OFFSET?

From: Greg Stark <gsstark(at)mit(dot)edu>
To: "D(dot) Dante Lorenso" <dante(at)lorenso(dot)com>
Cc: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>,pgsql-performance(at)postgresql(dot)org
Subject: Re: [PERFORM] Find original number of rows before applied LIMIT/OFFSET?
Date: 2004-01-07 19:41:04
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-generalpgsql-performance
"D. Dante Lorenso" <dante(at)lorenso(dot)com> writes:

> Any thoughts?  Sure, the PHP function I'm using above 'works', but is it
> the most efficient?  I hope I'm not actually pulling all 100,000 records
> across the wire when I only intend to show 10 at a time.  See what I'm
> getting at?

I tend to do it using a separate select count(*). My thinking is that the
count(*) query can be simplified and exclude things like the ORDER BY clause
and any select list entries that require extra work. It can often even exclude
whole joins.

By doing a separate query I can do that extra work only for the rows that i
actually need for display. Hopefully using an index to pull up those rows. And
do the count(*) in the most efficient way possible, probably a sequential scan
with no joins for foreign keys etc.

But I suspect the two methods both work out to suck about equally.


In response to

pgsql-performance by date

Next:From: Greg StarkDate: 2004-01-07 19:45:24
Subject: RAID array stripe sizes
Previous:From: JeffDate: 2004-01-07 17:20:15
Subject: Re: Index creation

pgsql-general by date

Next:From: Tom LaneDate: 2004-01-07 20:04:59
Subject: Re: VACUUM VERBOSE ANALYZE does not work on 7.4.1
Previous:From: Együd CsabaDate: 2004-01-07 19:37:30
Subject: Re: Query performance question on a large table

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group