| From: | David Rowley <dgrowleyml(at)gmail(dot)com> |
|---|---|
| To: | "Dirschel, Steve" <steve(dot)dirschel(at)thomsonreuters(dot)com> |
| Cc: | "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org>, "Wong, Kam Fook (TR Technology)" <kamfook(dot)wong(at)thomsonreuters(dot)com> |
| Subject: | Re: Query performance going from Oracle to Postgres |
| Date: | 2023-09-07 02:28:17 |
| Message-ID: | CAApHDvqr31P5fgMot0y=dSsXmHOf9AT7g0E_+LWtJ7Z7dgK0sA@mail.gmail.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
On Thu, 7 Sept 2023 at 11:14, Dirschel, Steve
<steve(dot)dirschel(at)thomsonreuters(dot)com> wrote:
> select count(historyeve0_.HISTORY_EVENT_SID) as col_0_0_ from hist28.history_event_display_timestamp_20230301 historyeve0_ where historyeve0_.IS_DELETED=0
> history_event_sid | character varying(32) | | not null |
Do you get an Index Only Scan if you use count(*) instead of
count(historyeve0_.HISTORY_EVENT_SID)?
It seems that column is the only one missing from the index and that
might be the only thing that's stopping PostgreSQL using an Index Only
Scan. Perhaps Oracle realises that the column is NOT NULL so can do
count(*) instead of counting the non-nulls from that column. That may
allow it to Index Only Scan? I'm not versed in reading Oracle plans so
don't know if it's possible to tell.
David
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Peter Geoghegan | 2023-09-07 03:07:47 | Re: Query performance going from Oracle to Postgres |
| Previous Message | Laurenz Albe | 2023-09-06 21:14:37 | Re: Query performance going from Oracle to Postgres |