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

Re: How to check a table content efficiently? With LIMIT and OFFSET?

From: Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>
To: Stefan Keller <sfkeller(at)gmail(dot)com>
Cc: Alban Hertroys <dalroi(at)solfertje(dot)student(dot)utwente(dot)nl>, pgsql-general List <pgsql-general(at)postgresql(dot)org>
Subject: Re: How to check a table content efficiently? With LIMIT and OFFSET?
Date: 2011-05-29 23:35:40
Message-ID: 4DE2D84C.6080507@postnewspapers.com.au (view raw or flat)
Thread:
Lists: pgsql-general
On 05/30/2011 05:55 AM, Stefan Keller wrote:
> Hi Alban
>
> On 2011/5/29 Alban Hertroys wrote:
>> On 29 May 2011, at 19:45, Stefan Keller wrote:
>>
>>> But I'm hesitating to use ANALYZE for two reasons:
>>> 1. It's very slow: it repeadly takes 59000 ms on my machine.
>>
>> ANALYZE on a single table takes 59s?!? That's _really_ long. How big is that table (it has about 180k rows, you did provide that information, but that's not much at all) and how many indexes are on it? Are you sure you're not overburdening your hardware in some way?
>>
>> Or are you in fact talking about a different command? For example, ANALYZE (without specifying a table) or VACUUM ANALYZE<table>?
>
> You are right: I used ANALYZE (without specifying a table). But this
> still takes about 1 to 3 sec which is about 100 times slower than

Hmm, ok. I would've expected ANALYZE on the table to be much, much 
faster than your LIMIT ... OFFSET query. If the LIMIT ... OFFSET 
approach works better for you, use that.

> SELECT (count(*) = 1) FROM (SELECT osm_id FROM planet_osm_point LIMIT
> 1 OFFSET 100000) tmp;

Instead of (count(*)=1) try writing:

SELECT EXISTS(SELECT osm_id FROM planet_osm_point LIMIT 1 OFFSET 100000);

(untested but I'm pretty sure that's right). Just a readability tweak, 
it shouldn't make any real difference in performance.

> SELECT reltuples FROM pg_class WHERE relname = 'planet_osm_point';

That is using cached statistics. If you have just done a batch update 
then it is *not* trustworthy without running ANALYZE tablename; first .

--
Craig Ringer

In response to

pgsql-general by date

Next:From: Edison SoDate: 2011-05-30 00:19:55
Subject: Re: max_connections proposal
Previous:From: Craig RingerDate: 2011-05-29 23:27:18
Subject: Re: max_connections proposal

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