1

So I've got an index only scan returning 750k rows, and sucking it into a cte and doing count star is taking .5 seconds. It's barely using any iops, and maxing out the instance to 16xlarge isn't moving the needle. Switched to bitmap heap scan and it's still giving me .5 seconds. What are some alternatives (other than using a mat view) I can try to speed it up? Or is this just postgres v10 at its finest?

user433342
  • 859
  • 1
  • 7
  • 26
  • See: https://wiki.postgresql.org/wiki/Slow_Counting and https://wiki.postgresql.org/wiki/Count_estimate – Will Bickford Sep 06 '18 at 02:47
  • Possible duplicate of [If PostgreSQL count(\*) is always slow how to paginate complex queries?](https://stackoverflow.com/questions/16916633/if-postgresql-count-is-always-slow-how-to-paginate-complex-queries) – Will Bickford Sep 06 '18 at 02:48
  • 1
    The question is very unclear. You should show the table definition, the query and the plan (with analyze and buffers options) at least. – Roman-Stop RU aggression in UA Sep 06 '18 at 05:47
  • Please **[edit]** your question and add the `create table` statements for the tables in question (including all indexes), the query you are using and the [execution plan](https://www.postgresql.org/docs/current/static/using-explain.html) generated using **`explain (analyze, buffers, verbose)`** (not just a plain "explain") [Formatted text](http://stackoverflow.com/help/formatting) please, [no screen shots](http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557) –  Sep 06 '18 at 06:32
  • Ok I’ll change it to select limit 1 (just to make sure the cte executes and count isn’t an issue) and include the verbose explain, which is new to me. Thanks for the tips. – user433342 Sep 06 '18 at 12:24

0 Answers0