Optimize a max() aggregate that works for "no rows", too

Question

I have a query that I am trying to optimize for PostgreSQL 9.2:

select coalesce(max(id),0) as m from tbl

It takes for forever to run, so I figured I could rewrite it as

select id from tbl order by id desc limit 1

except it needs to return 0 if there are no rows in the table. I've tried a few combinations of case statements, but they don't seem to work. Any suggestions?

Plan for an empty table:

Aggregate (cost=11.25..11.26 rows=1 width=4)
 -> Seq Scan on tbl (cost=0.00..11.00 rows=100 width=4)

The cost is 58k with a table with 1,190,000 rows, same execution plan though.

Answer 1

except it needs to return 0 if there are no rows in the table

SELECT COALESCE(max(id), 0) FROM tbl;

This works because max() - like all aggregate functions - always returns a row, even if there are no rows in the table.
_{Fine point: this also returns 0 if all existing rows of tbl have id IS NULL.}

See:

• Return zero if no record is found

When no row can be returned, wrap the SELECT it in a subquery:

SELECT COALESCE((SELECT max(id) FROM tbl), 0);  -- not necessary for max()

The key to performance is an index on tbl.id Could be a primary (or unique) key constraint, which is implemented using a unique index on the column. Or a plain b-tree index:

CREATE index foo on tbl(id)

See:

• Return a value if no record is found
• How to display a default value when no match found in a query?

Answer 2

Maybe this:

select
    case
        when (select id from tbl limit 1) is null then 0
        else (select id from tbl order by id desc limit 1)
    end;