2

I have a bunch of tables that I'm iterating through, and some of them have no rows (i.e. just a table of headers with no data). ex: SELECT my_column FROM my_schema.my_table LIMIT 1 returns an empty result set.

What is the absolute fastest way to check that a table is one of these tables with no rows?

I've considered: SELECT my_column FROM my_schema.my_table LIMIT 1 or SELECT * FROM my_schema.my_table LIMIT 1 followed by an if result is None(I'm working in Python). Is there any faster way to check?

singmotor
  • 3,930
  • 12
  • 45
  • 79

3 Answers3

4

This is not faster than your solution but returns a boolean regadless:

select exists (select 1 from mytable)
Clodoaldo Neto
  • 118,695
  • 26
  • 233
  • 260
3
select exists (select * from myTab);

or 

select 1 where exists (select * from myTab)

or even

SELECT reltuples FROM pg_class WHERE oid = 'schema_name.table_name'::regclass;

The 3rd example uses the estimator to estimate rows, which may not be 100% accurate, but may be a tad bit faster.

Joe Love
  • 5,594
  • 2
  • 20
  • 32
-5
SELECT COUNT(*) FROM table_name limit 1;

Try this code .

CanyoucSharp
  • 33
  • 5
  • Depending on the database, that could be very expensive if the table *isn't* empty. – chepner Apr 18 '17 at 20:38
  • 2
    No it ain't. You limit the result rows to one, but it is only one row anyway. (LIMIT is executed after COUNT). – Thorsten Kettner Apr 18 '17 at 20:41
  • Not to be rude, but this may be one of the worst possible answers in terms of speed. I cannot imagine a slower way to get the information requested. I only say this to recommend against anyone seeing this in the future and thinking it's a viable solution when speed is a primary concern-- the limit 1 clause is a red herring and won't affect the results in any way except perhaps a very very small amount of slowdown. You are free to count the rows in the table under certain situations, but it should be avoided when just checking if a row exists as it's about the slowest way to find that. – Joe Love Apr 19 '17 at 15:14