| From: | Michael Lewis <mlewis(at)entrata(dot)com> |
|---|---|
| To: | Justin Pryzby <pryzby(at)telsasoft(dot)com> |
| Cc: | Marco Colli <collimarco91(at)gmail(dot)com>, pgsql-performance(at)lists(dot)postgresql(dot)org |
| Subject: | Re: Extremely slow count (simple query, with index) |
| Date: | 2019-08-22 17:37:04 |
| Message-ID: | CAHOFxGpxMsgxQZ-H1C5v_A5FK-XqdTQ_m4agJWD0FfNPOrfMww@mail.gmail.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-performance |
>
> You can SELECT reltuples FROM pg_class WHERE oid='subscriptions'::oid, but
> its
> accuracy depends on frequency of vacuum (and if a large delete/insert
> happened
> since the most recent vacuum/analyze).
>
This only seems helpful to find approx. count for the entire table, without
considering the WHERE condition.
Marco,
As Justin pointed out, you have most of your time in the bitmap heap scan.
Are you running SSDs? I wonder about tuning effective_io_concurrency to
make more use of them.
"Currently, this setting only affects bitmap heap scans."
https://www.postgresql.org/docs/current/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-ASYNC-BEHAVIOR
Also, how many million rows is this table in total? Have you considered
partitioning?
| From | Date | Subject | |
|---|---|---|---|
| Next Message | legrand legrand | 2019-08-22 17:43:10 | Re: Erratically behaving query needs optimization |
| Previous Message | MichaelDBA | 2019-08-22 13:25:36 | Re: Extremely slow count (simple query, with index) |