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

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