Is there a way to find the number of results returned that is faster than running the entire query

Question

I'm guessing the answer is no but in Postgres is there a way to find the number of results returned that is faster than running the entire query? In other words if you have a query that takes a really long time can it be done faster if you only want to know the number of rows returned?

score 2 · Accepted Answer · answered Sep 27 '12 at 23:08

2

Not sure if it will give you the scale of improvement you're after, but a select count() will always be faster than a select some fields query because the result set doesn't have to be returned to the client.

The server still needs to go through the entire selection process though, so that part of the operation will take exactly the same time. In short, the amount of time you win with a count() decreases when the query is more complex or when the result set is quite short.

I hope this makes sense...

On the other hand, if you're worried about the execution time of your query, your first stop should always be EXPLAIN (also available in a highly visual form in pgAdminIII, as explained here)

enter image description here

answered Sep 27 '12 at 23:08

fvu

32,488
6
61
79

1

Also to mention: selection counts might only need index scans, whereas a selection of fields may need much more. I suspect the whole selection process may end up with quite different EXPLAINations for some queries. – JayC Sep 27 '12 at 23:23
@JayC COUNT() in Postgres uses a full sequential table scan. – Dondi Michael Stroma Sep 28 '12 at 00:30
@DondiMichaelStroma I don't know where you got that information, but that's not right in general. It depends on the query and the indexes you have. – JayC Sep 28 '12 at 02:51
@DondiMichaelStroma If you don't believe me, take a look at this sql fiddle: http://sqlfiddle.com/#!12/b9bb4/3 – JayC Sep 28 '12 at 02:53
1

@JayC http://www.postgresql.org/docs/9.1/static/functions-aggregate.html: "A query like 'SELECT count(*) FROM sometable' will be executed by PostgreSQL using a sequential scan of the entire table." – Dondi Michael Stroma Sep 28 '12 at 03:00
Ok, I suppose that's true, at least up until 9.1. It's different with 9.2, which is what my sqlfiddle shows. See http://www.postgresql.org/docs/9.2/static/functions-aggregate.html "A query like: `SELECT count(*) FROM sometable;` will require effort proportional to the size of the table: PostgreSQL will need to scan either the entire table *or the entirety of an index which includes all rows in the table*.". I'm surprised the only added that feature recently. – JayC Sep 28 '12 at 03:14

score 2 · Answer 2 · edited May 23 '17 at 10:24

count() is exact, but notoriously slow, as it has to verify that rows are still alive. The exception being the new index-only scan in PostgreSQL 9.2 - under optimal conditions and if a suitable index is available.

Robert Haas wrote about fast counting in his blog.

If your query retrieves all rows from a table
and your statistics are up to date
and your result does not have to be 100 % exact (changes since the last ANALYZE), there is a much faster shortcut:

SELECT reltuples::bigint AS estimate
FROM   pg_class
WHERE  oid = 'myschema.mytable'::regclass;

Is there a way to find the number of results returned that is faster than running the entire query

4 Answers4