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

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