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

Hi Craig

Thanks for the answer. I also thought about this. You mean something like this?

SELECT reltuples FROM pg_class WHERE relname = 'mytable';
182820 (rows)

That seams reasonably fast compared to count(*).

But I'm hesitating to use ANALYZE for two reasons:
1. It's very slow: it repeadly takes 59000 ms on my machine.
2. There's an autovacuum background process which already does the
job, doesn't it?

Yours, Stefan

2011/5/29 Craig Ringer <craig@postnewspapers.com.au>:
> On 05/29/2011 05:45 AM, Stefan Keller wrote:
>>
>> Hi,
>>
>> That's my solution candidate:
>>
>> CREATE OR REPLACE FUNCTION isnotempty() RETURNS boolean AS '
>>   SELECT (count(*) = 1)
>>   FROM (SELECT osm_id FROM planet_osm_point LIMIT 1 OFFSET 100000) tmp
>> ' LANGUAGE SQL;
>
> LIMIT and OFFSET are often no more efficient than count(*). You're still
> likely to need a full table scan.
>
> Here's how I'd do it: I'd ANALYZE the table, then check the table statistics
> to see that they looked to be within reasonable bounds. That way you not
> only check the import, but in the process you ensure the statistics used by
> the query planner are up to date. Since ANALYZE only tests a sampling of
> records it does pretty much what you want, something that it's not so easy
> to do in SQL.
>
> --
> Craig Ringer
>