4

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.

  • 1
    How reliable is this "knowledge"? Is field1 always the same due to the nature of the src CTE, due to the DB design, or due to current business rules? Personally, my first take on a piece of code will by preference make no assumptions beyond those built into the DB design (I may not test a NOT NULL column to make sure it's not NULL). If situations could change, especially without needing to change the DB structure notably, you might have 999 rows where field1 is the same - and one where it's not. Presumably, picking the one wouldn't be ideally representative of the underlying data. – RDFozz Oct 11 '17 at 18:16
5

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.

  • I am not sure that materializing yet another CTE even if it contain only single row is more efficient then just searching a maximum value in the dataset which should be scanned anyway. – Abelisto Oct 11 '17 at 17:51
  • @Abelisto good point. That's why I am not accepting it – oᴉɹǝɥɔ Oct 11 '17 at 17:53
  • Would be interested in whether this makes a noticeable change in query speed, or a notable improvement in the explain plan. – RDFozz Oct 11 '17 at 18:10
  • This is a good idea - and probably not less efficient than the original query - but the main source of inefficiency may be the materialization of src. You may get better improvements by changing the CTE to a derived table or possibly with other/different modifications. Without further details on that part of the code, I don't think we can provide more help. – ypercubeᵀᴹ Oct 12 '17 at 10:17
2

You might want the first aggregate provided by the first_last_agg extension.

There was some discussion about an aggregate which would throw an error if any of its inputs differed, and return the common value if they were all the same. But I don't think it was ever finished/released.

0

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.

If they're the same, then unless there was a performance difference I would not be writing it like this,

SELECT MAX(field1), array_agg(field2)
FROM src

But like this,

SELECT field1, array_agg(field2)
FROM src
GROUP BY field1;

Shy of that, I would go with MIN/MAX do you have any reason to believe it's not fast enough or that you could do something faster?

  • Sure this is an option but efficiency wise it is the same if not less efficient as it needs to compare field1 for every row to detect grouppings. I was looking for an aggregate which would, for example, grab the first value and ignore the rest, avoiding array compare operations (field1 is an array). – oᴉɹǝɥɔ Oct 11 '17 at 16:26
  • I imagine it's only very slightly slower and much more correct form. The first_value can be done but it also requires ordering, as in the case of Ordered-Set Aggregates because it requires ordering there is a lot of compare operations. – Evan Carroll Oct 11 '17 at 16:30
  • Have you tried rewriting your query without the CTE using a derived table? I imagine that speeds it up a good bit. – Evan Carroll Oct 11 '17 at 16:33
  • CTE in my case is necessary. The real reason I am trying to squeeze every bit of performance is because this SQL is sitting in a function which runs in a SELECT on a long list of rows. – oᴉɹǝɥɔ Oct 11 '17 at 16:38
  • A non-recursive CTE is never necessary. WITH t AS ( qry ) vs SELECT * FROM ( qry ) AS t. – Evan Carroll Oct 11 '17 at 16:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.