Would you use distinct, or maybe a window function to do this?-CodePudding

Is there someway to shortcut it? window function? Group by? Or is distinct the right approach?

select
     distinct itemid item_id
    from
        unitactuals.unitserials
    where
        mfgdate > $begin_date_range
        and mfgdate < $end_date_range
        and siteid = $site_id
        and serial like ($serial_prefix || '%')

CodePudding user response：

There is no better option than DISTINCT if you need to avoid duplicate results.

If the DISTINCT really is what makes that query slow, the only thing you can do is to set work_mem as high as you can for this query.

If DISTINCT is not the bottleneck, perhaps you can add indexes to speed up the query.

First, I would rewrite the query to use the string prefix operator:

SELECT DISTINCT itemid item_id
FROM unitactuals.unitserials
WHERE mfgdate > $begin_date_range
  AND mfgdate < $end_date_range
  AND siteid = $site_id
  AND serial ^@ $serial_prefix;

Then these two indexes might speed up the query:

CREATE INDEX ON unitactuals.unitserials (siteid, mfgdate);
CREATE INDEX ON unitactuals.unitserials USING spgist (serial);