17

Is there a faster way in PostgreSQL to essentially do an if on several rows?

Say I have a table

ticket | row | archived
1      | 1   | true
1      | 2   | true
1      | 3   | true
2      | 1   | false
2      | 2   | true

Is there any way I could do an if statement across down the column where ticket = ? So that where ticket = 1 would be true because

true && true && true = true

and where ticket = 2 would be false because

false && true = false

Or should I just stick with

SELECT ( (SELECT COUNT(*) FROM table WHERE ticket = 1)
       = (SELECT COUNT(*) FROM table WHERE ticket = 1 AND archived = true) )
Erwin Brandstetter
  • 539,169
  • 125
  • 977
  • 1,137
mouckatron
  • 1,229
  • 2
  • 12
  • 23

3 Answers3

22

Aggregate function bool_and()

Simple, short, clear:

SELECT bool_and(archived)
FROM   tbl
WHERE  ticket = 1;

The manual:

true if all input values are true, otherwise false

Subquery expression EXISTS

Like @Mike provided.

Faster. But you have to additionally check whether any rows with ticket = 1 exist at all, or you'll get incorrect results for non-existing tickets:

SELECT EXISTS (SELECT 1 FROM tbl WHERE ticket=1)
       AND NOT
       EXISTS (SELECT 1 FROM tbl WHERE ticket=1 AND archived = FALSE);

Indices

Both forms can and will use an index like:

CREATE index tbl_ticket_idx ON tbl (ticket);

.. which makes both fast, but the EXISTS query faster, because this form can stop to scan as soon as the first matching row is found. There is hardly any difference between the two queries with only few rows per ticket, but a substantial difference for many rows per ticket.

To make use of index-only scans in pg 9.2 you would need a multi-column index of the form:

CREATE index tbl_ticket_archived_idx ON tbl (ticket, archived);

This one is better in any case most cases and any version of PostgreSQL. Due to data alignment, adding a boolean to the integer in the index will not make the index grow at all. Added benefit for hardly any cost.

However, indexed columns prevent HOT (Heap Only Tuple) updates. Say, an UPDATE changes only the column archived. If the column isn't used by any index (in any way), the row can be HOT updated. Else, this shortcut cannot be taken. More on HOT updates:

It all depends on your actual workload.

Erwin Brandstetter
  • 539,169
  • 125
  • 977
  • 1,137
  • Definitely a clean solution! Curious how the perf is, and can it take advantage of index-only scans in 9.2? – Mike Christensen Oct 22 '12 at 19:04
  • 1
    @Mike: I dug a little deeper, ran some tests and added a bit about indexes and performance to my answer. – Erwin Brandstetter Oct 22 '12 at 20:31
  • @MikeChristensen I appreciate your time to answer! I'm accepting Erwin's answer though, because the much shorter code will sit better in my query! The performance hit shouldn't be too much, I don't expect this query to run on more than a few rows per ticket. Thanks guys! – mouckatron Oct 23 '12 at 09:50
  • @mouckatron: I added a bit about HOT updates. Might be of interest to you, if you have lots of `UPDATE`s. – Erwin Brandstetter Oct 23 '12 at 21:43
  • @ErwinBrandstetter I'm always impressed with your knowledge of Postgres. I upvoted dozens of your answers by now. – isapir Jul 16 '18 at 19:37
5

How about something like:

select not exists (select 1 from table where ticket=1 and not archived)

I think this might be advantageous over comparing the counts, as a count may or may not use an index and really all you need to know is if any FALSE rows exist for that ticket. I think just creating a partial index on ticket could be incredibly fast.

SQL Fiddle

Mike Christensen
  • 82,887
  • 49
  • 201
  • 310
  • Count will not use an index *if* it is a version of PostgreSQL prior to 9.2. Just learned that recently. – JayC Oct 22 '12 at 16:13
  • 1
    @JayC: of course count() can use an index. `select count(*) from foo where some_col = 1` can use an index on some_col if that doesn't return too many rows. `count()` will not use an index if you are counting ***all*** rows in a table (without any restrictions) – a_horse_with_no_name Oct 22 '12 at 16:24
  • @a_horse_with_no_name - Oh thanks for clearing that up! I was a bit curious after [this](http://stackoverflow.com/questions/13014277/why-is-this-count-query-so-slow) recent question, which I see you also commented on. – Mike Christensen Oct 22 '12 at 16:34
  • @a_horse_with_no_name: you are right; I shouldn't have let Mike confuse me ;-) – JayC Oct 23 '12 at 15:59
1
select not false = any (
        select archived
        from foo
        where ticket = 1
    )

SQL Fiddle

Clodoaldo Neto
  • 108,856
  • 25
  • 211
  • 247