Postgresql – Does bool_and short-circuit function calls with arguments

aggregatefunctionsoptimizationpostgresql

Suppose I have the following query:

SELECT a.*
FROM a
CROSS JOIN b
GROUP BY a.id -- Primary key
HAVING bool_and(expensive_comparison(a, b))

What will PostgreSQL do with this query? Will it process expensive_comparison on all pairs, or will it stop and return immediately for a given for a given a.id?

Is there a more efficient, equivalent way to write this query without resorting to procedural code?

I'm using PostgreSQL 9.3, but if it's changed in any newer versions, that would be relevant info.

Best Answer

It depends on whether the funciton is IMMUTABLE or not. All tests done on PostgreSQL 9.5.

CREATE FUNCTION f_expensive_volatile ()
RETURNS bool AS $$
  SELECT true
  FROM pg_sleep(1)
$$ LANGUAGE SQL
VOLATILE;

CREATE FUNCTION f_expensive_stable ()
RETURNS bool AS $$
  SELECT true
  FROM pg_sleep(1)
$$ LANGUAGE SQL
STABLE;

CREATE FUNCTION f_expensive_immutable ()
RETURNS bool AS $$
  SELECT true
  FROM pg_sleep(1)
$$ LANGUAGE SQL
IMMUTABLE;

Now we create sample data.

CREATE TABLE f AS
SELECT 1 AS id, *
FROM generate_series(1,100) AS gs(x);

Now on the IMMUTABLE one, note Execution time: 0.138 ms,

EXPLAIN ANALYZE
  SELECT id
  FROM f
  GROUP BY id
  HAVING bool_and( f_expensive_immutable() );

                                             QUERY PLAN                                              
-----------------------------------------------------------------------------------------------------
 HashAggregate  (cost=2.50..2.51 rows=1 width=4) (actual time=0.083..0.083 rows=1 loops=1)
   Group Key: id
   Filter: bool_and(true)
   ->  Seq Scan on f  (cost=0.00..2.00 rows=100 width=4) (actual time=0.015..0.030 rows=100 loops=1)
 Planning time: 1001.328 ms
 Execution time: 0.138 ms
(6 rows)

And on the STABLE one... note me not waiting around

EXPLAIN ANALYZE
  SELECT id
  FROM f
  GROUP BY id
  HAVING bool_and( f_expensive_stable() );

And also this exemplifies me not waiting around...

EXPLAIN ANALYZE
  SELECT id
  FROM f
  GROUP BY id
  HAVING bool_and( f_expensive_volatile() );

With arguments

CREATE FUNCTION f_expensive_immutable (int)
RETURNS bool AS $$
  SELECT true
  FROM pg_sleep(1)
$$ LANGUAGE SQL
IMMUTABLE;

EXPLAIN ANALYZE
  SELECT id
  FROM f
  GROUP BY id
  HAVING bool_and( f_expensive_immutable(id) );

Yes, it seems that optimization is lost if you provide arguments

Related Solutions

Sql-server – How recording of every change of a row in a database is generaly stored

The normal way do do audit logging of this sort is to have a shadow table and log changes with triggers on the base table you are auditing. The other tables can be placed on a different physical disk if you need to to that for performance, and you can put indexes on them if you need to support quick retrieval of the data.

The tables will have roughly the same structure as your original tables, but will have a datetime column for when the change took place and a marker for whether the row was inserted, changed or deleted. Sequencing the versions can be done by the time stamp.

The change date can be done by making the datetime column not null with a default of getdate(); an audit user column will capture the user with a not null column defaulted to Suser_Sname(). Assuming the actual user is being impersonated in the session this will capture the identity of the user making the change.

The database has no way to be aware of the IP address connecting to a web server. The application will have to explicitly capture and log the IP address with the transaction.

If you have a large number of tables you wish to audit, you can use the metadata from the system data dictionary to generate the triggers programmatically.

This solution is by far the best for several reasons:

It captures any changes to the table, not just those made by the application.
The audit tables can be put on a different set of disks to reduce the I/O load on your primary tables.
You can use a view based on a union of the table and audit log table to that will show the entire history including the current version.
You can index the audit log tables as necessary so that the audit users can query them responsively. As usual, index selection is a tradeoff between query performance and update overhead.

Postgresql – Most efficient way of bulk deleting rows from postgres

Your second option is far cleaner and will perform well enough to make that worth it. Your alternative is to build gigantic queries which will be quite a pain to plan and execute. In general you are going to be better off letting PostgreSQL do the work here. In general, I have found updates on tens of thousands of rows in the manner you are describing to perform adequately, but there is one important thing to avoid doing.

The way to do it is to use a select and a join in your delete.

DELETE FROM foo WHERE id IN (select id from rows_to_delete);

Under no circumstances should you as follows with a large table:

DELETE FROM foo WHERE id NOT IN (select id from rows_to_keep);

This will usually cause a nested loop antijoin which will make performance rather problematic. If you end up having to go that route, do this instead:

DELETE FROM foo 
WHERE id IN (select id from foo f 
          LEFT JOIN rows_to_keep d on f.id = d.id
              WHERE d.id IS NULL);

PostgreSQL is usually pretty good at avoiding bad plans but there are still cases involving outer joins which can make a big difference between good and bad plans.

This is wandering a little more far afield, but I figure it is worth mentioning because of how easy it is to go from the IN to NOT IN and watch query performance tank.

Best Answer

With arguments

Related Solutions

Sql-server – How recording of every change of a row in a database is generaly stored

Postgresql – Most efficient way of bulk deleting rows from postgres

Related Question