Postgresql – Aggregate function that picks any/first/last/random value

aggregatepostgresql

I run a query which looks like below

WITH src AS (
    ...
)
SELECT MAX(field1), array_agg(field2)
FROM src

I have knowledge that field1 is the same across all rows returned from src so I really don't really care whether the aggregate function used with it is MAX or MIN. In my case field1 is an array so I am curious if there is a more efficient way to grab any value of field1 which doesn't include overhead of comparing it with the previously saved MIN/MAX. If there was a function to grab the first / retain the last value of field1 that would be ideal.

Best Answer

I believe I found a more efficient SQL for what I am trying to achieve

WITH src AS (
    ...
)
, identical_field1 (
    SELECT field1 FROM src LIMIT 1
)
SELECT (SELECT field1 FROM identical_field1), array_agg(field2)
FROM src

Sub-select on field1 is evaluated only once and only requests one single row from src.

I am not marking it as an answer for now 'cause someone may still suggest a more efficient way.

Related Solutions

Sql-server – Why is an aggregate query significantly faster with a GROUP BY clause than without one

It looks like it is probably following an index on CreatedDate in order from lowest to highest and doing lookups to evaluate the SomeIndexedValue = 1 predicate.

When it finds the first matching row it is done, but it may well be doing many more lookups than it expects before it finds such a row (it assumes the rows matching the predicate are randomly distributed according to date.)

See my answer here for a similar issue

The ideal index for this query would be one on SomeIndexedValue, CreatedDate. Assuming that you can't add that or at least make your existing index on SomeIndexedValue cover CreatedDate as an included column then you could try rewriting the query as follows

SELECT MIN(DATEADD(DAY, 0, CreatedDate)) AS CreatedDate
FROM MyTable
WHERE SomeIndexedValue = 1

to prevent it from using that particular plan.

Postgresql – Combining two similar SQL queries

Will it work for you ?

SELECT SUM(CASE WHEN r <=LEAST(40,100) THEN motif ELSE 0 END) as sum1,
SUM(motif) as sum2
FROM (...) q  -- or AS q , whatever version you prefer
WHERE    q.r <= GREATEST(40,100)
GROUP BY crossvalnum;

Best Answer

Related Solutions

Sql-server – Why is an aggregate query significantly faster with a GROUP BY clause than without one

Postgresql – Combining two similar SQL queries

Related Question