| From: | David Rowley <dgrowleyml(at)gmail(dot)com> |
|---|---|
| To: | Nagaraj Raj <nagaraj(dot)sf(at)yahoo(dot)com> |
| Cc: | Michael Lewis <mlewis(at)entrata(dot)com>, Thomas Kellerer <shammat(at)gmx(dot)net>, Pgsql Performance <pgsql-performance(at)lists(dot)postgresql(dot)org> |
| Subject: | Re: Query performance issue |
| Date: | 2020-09-05 08:16:29 |
| Message-ID: | CAApHDvpUU3S4EOWE4vCDHSp+S4yxoH0hxH96GojPH7J9+ZhiAw@mail.gmail.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-performance |
On Sat, 5 Sep 2020 at 10:20, Nagaraj Raj <nagaraj(dot)sf(at)yahoo(dot)com> wrote:
> I added the index as you suggested and the planner going through the bitmap index scan,heap and the new planner is,
> HaOx | explain.depesz.com
In addition to that index, you could consider moving away from
standard SQL and use DISTINCT ON, which is specific to PostgreSQL and
should give you the same result.
EXPLAIN ANALYZE
SELECT DISTINCT ON (serial_no) serial_no,receivingplant,sku,r3_eventtime
FROM receiving_item_delivered_received
WHERE eventtype='LineItemdetailsReceived'
AND replenishmenttype = 'DC2SWARRANTY'
AND coalesce(serial_no,'') <> ''
ORDER BY serial_no,eventtime DESC;
The more duplicate serial_nos you have the better this one should
perform. It appears you don't have too many so I don't think this
will be significantly faster, but it should be a bit quicker.
David
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Michael Lewis | 2020-09-05 13:42:17 | Re: Query performance issue |
| Previous Message | Nagaraj Raj | 2020-09-04 22:20:06 | Re: Query performance issue |