5

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
tandoan
  • 190
  • 1
  • 2
  • 11
  • 1
    What is `a m` ? Typo? should it be `select coalesce(max(id),0) AS m from tbl` – wildplasser Jan 13 '13 at 17:47
  • @a_horse_with_no_name id is the primary key This is the 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 – tandoan Jan 13 '13 at 18:49
  • This is how I'd expect the plan to look like: http://explain.depesz.com/s/hp8 (the table has 5000000 rows) –  Jan 13 '13 at 18:59
  • autovacuum is on. after an analyze, the row estimate is 1, cost is 59k. Running set enable_seqscan=off still has it doing a seq scan – tandoan Jan 13 '13 at 19:09
  • 1
    There must be something you are not telling us. It's hard to believe PostgreSQL 9.2 actually does a full table scan when doing a max() on the PK (see my link to explain.depesz.com). –  Jan 13 '13 at 19:10
  • See also here: http://sqlfiddle.com/#!12/77e30/1 –  Jan 13 '13 at 19:13
  • Hmm. It was recently upgraded from 8.3.16 to 9.2. Would that have any bearing? – tandoan Jan 13 '13 at 19:22

2 Answers2

6

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:

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:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
0

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;
creaktive
  • 5,193
  • 2
  • 18
  • 32