CODEWITHSUNDEEP
postgresql
Alex. S.
Alex. S.

Reputation: 147186

How do you efficiently determine if a Postgres table has rows

I did this tests and the results seems the count function scale linearly. I have another function relying strongly in the efficiency to know if there are any data, so I would like to know how to replace this select count(*) with another more efficient (maybe constant?) query or data structure.

psql -d testdb -U postgres -f truncate_and_insert_1000_rows.sql > NUL

psql -d testdb -U postgres -f count_data.sql

--------------------------------------------------------------------------------

Aggregate (cost=36.75..36.76 rows=1 width=0) (actual time=0.762..0.763 rows=1 loops=1) -> Seq Scan on datos (cost=0.00..31.40 rows=2140 width=0) (actual time=0.02 8..0.468 rows=1000 loops=1) Total runtime: 0.846 ms (3 filas)

psql -d testdb -U postgres -f truncate_and_insert_10000_rows.sql > NUL

psql -d testdb -U postgres -f count_data.sql

--------------------------------------------------------------------------------

Aggregate (cost=197.84..197.85 rows=1 width=0) (actual time=6.191..6.191 rows= 1 loops=1) -> Seq Scan on datos (cost=0.00..173.07 rows=9907 width=0) (actual time=0.0 09..3.407 rows=10000 loops=1) Total runtime: 6.271 ms (3 filas)

psql -d testdb -U postgres -f truncate_and_insert_100000_rows.sql > NUL

psql -d testdb -U postgres -f count_data.sql

--------------------------------------------------------------------------------

Aggregate (cost=2051.60..2051.61 rows=1 width=0) (actual time=74.075..74.076 r ows=1 loops=1) -> Seq Scan on datos (cost=0.00..1788.48 rows=105248 width=0) (actual time= 0.032..46.024 rows=100000 loops=1) Total runtime: 74.164 ms (3 filas)

psql -d prueba -U postgres -f truncate_and_insert_1000000_rows.sql > NUL

psql -d testdb -U postgres -f count_data.sql

--------------------------------------------------------------------------------

Aggregate (cost=19720.00..19720.01 rows=1 width=0) (actual time=637.486..637.4 87 rows=1 loops=1) -> Seq Scan on datos (cost=0.00..17246.60 rows=989360 width=0) (actual time =0.028..358.831 rows=1000000 loops=1) Total runtime: 637.582 ms (3 filas)

the definition of data is

CREATE TABLE data
(
  id INTEGER NOT NULL,
  text VARCHAR(100),
  CONSTRAINT pk3 PRIMARY KEY (id)
);

Upvotes: 7

Views: 10957

Answers (6)

Milen A. Radev
Milen A. Radev

Reputation: 62563

You may find this useful.

Upvotes: 0

Michael Buen
Michael Buen

Reputation: 39393

select exists(select * from your_table_here) as has_row

Upvotes: 7

Tometzky
Tometzky

Reputation: 23890

select true from table limit 1;

Upvotes: 18

Patryk Kordylewski
Patryk Kordylewski

Reputation: 1269

Try this:

SELECT t.primary_key IS NOT NULL FROM table t LIMIT 1;

You will get TRUE if there are records and NULL if there are none.

Upvotes: 2

Mark Brady
Mark Brady

Reputation:

If all you care about is 1 row or no rows. Limit your query to the first row - why count all of the rows just to find out if there's 1 or more, or zero...

use the equivalent of ROWNUM = 1 or TOP 1 or whatever postgres gives you.

Upvotes: 1

warren
warren

Reputation: 33435

How a count on the primary key field where it is NOT NULL, limiting the query at 1 response?

Since a primary key must exist, if there is one, you have data, yes?

Upvotes: 0

Related Questions