From: MichaelDBA <MichaelDBA@sqlexec.com>
To: Marco Colli <collimarco91@gmail.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Extremely slow count (simple query, with index)
Date: Thu, 22 Aug 2019 09:25:36 -0400
Message-ID: <02a535cc-ad71-2380-e15a-cf6123fb65d7@sqlexec.com> (raw)
In-Reply-To: <CAFvCgN4UijKTYiOF61Tyd+gHvF_oqnMabatS9+DcX+_PK2SHRw@mail.gmail.com>
References: <CAFvCgN4UijKTYiOF61Tyd+gHvF_oqnMabatS9+DcX+_PK2SHRw@mail.gmail.com>
Hi Marco,
Since you said approximates would be good enough, there are two ways to
do that. Query pg_class.reltuples or pg_stat_user_tables.n_live_tup.
Personally, I prefer the pg_stat_user tables since it is more current
than pg_class table, unless you run ANALYZE on your target table before
querying pg_class table. Then of course you get results in a few
milliseconds since you do not incur the tablescan cost of selecting
directly from the target table.
Regards,
Michael Vitale
Marco Colli wrote on 8/22/2019 8:44 AM:
> Hello!
>
> Any help would be greatly appreciated.
> I need to run these simple queries on a table with millions of rows:
>
> ```
> SELECT COUNT(*) FROM "subscriptions" WHERE
> "subscriptions"."project_id" = 123;
> ```
>
> ```
> SELECT COUNT(*) FROM "subscriptions" WHERE
> "subscriptions"."project_id" = 123 AND "subscriptions"."trashed_at" IS
> NULL;
> ```
>
> The count result for both queries, for project 123, is about 5M.
>
> I have an index in place on `project_id`, and also another index on
> `(project_id, trashed_at)`:
>
> ```
> "index_subscriptions_on_project_id_and_created_at" btree (project_id,
> created_at DESC)
> "index_subscriptions_on_project_id_and_trashed_at" btree (project_id,
> trashed_at DESC)
> ```
>
> The problem is that both queries are extremely slow and take about 17s
> each.
>
> These are the results of `EXPLAIN ANALIZE`:
>
>
> ```
> QUERY PLAN
> --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> Aggregate (cost=2068127.29..2068127.30 rows=1 width=0) (actual
> time=17342.420..17342.420 rows=1 loops=1)
> -> Bitmap Heap Scan on subscriptions (cost=199573.94..2055635.23
> rows=4996823 width=0) (actual time=1666.409..16855.610 rows=4994254
> loops=1)
> Recheck Cond: (project_id = 123)
> Rows Removed by Index Recheck: 23746378
> Heap Blocks: exact=131205 lossy=1480411
> -> Bitmap Index Scan on
> index_subscriptions_on_project_id_and_trashed_at
> (cost=0.00..198324.74 rows=4996823 width=0) (actual
> time=1582.717..1582.717 rows=4994877 loops=1)
> Index Cond: (project_id = 123)
> Planning time: 0.090 ms
> Execution time: 17344.182 ms
> (9 rows)
> ```
>
>
> ```
> QUERY PLAN
> --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> Aggregate (cost=2047881.69..2047881.70 rows=1 width=0) (actual
> time=17557.218..17557.218 rows=1 loops=1)
> -> Bitmap Heap Scan on subscriptions (cost=187953.70..2036810.19
> rows=4428599 width=0) (actual time=1644.966..17078.378 rows=4994130
> loops=1)
> Recheck Cond: ((project_id = 123) AND (trashed_at IS NULL))
> Rows Removed by Index Recheck: 23746273
> Heap Blocks: exact=131144 lossy=1480409
> -> Bitmap Index Scan on
> index_subscriptions_on_project_id_and_trashed_at
> (cost=0.00..186846.55 rows=4428599 width=0) (actual
> time=1566.163..1566.163 rows=4994749 loops=1)
> Index Cond: ((project_id = 123) AND (trashed_at IS NULL))
> Planning time: 0.084 ms
> Execution time: 17558.522 ms
> (9 rows)
> ```
>
> What is the problem?
> What can I do to improve the performance (i.e. count in a few seconds)?
>
> I have also tried to increase work_mem from 16MB to 128MB without any
> improvement.
> Even an approximate count would be enough.
> Postgresql v9.5
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: MichaelDBA@sqlexec.com, collimarco91@gmail.com, pgsql-performance@lists.postgresql.org
Subject: Re: Extremely slow count (simple query, with index)
In-Reply-To: <02a535cc-ad71-2380-e15a-cf6123fb65d7@sqlexec.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox