Subject: Re: Extremely slow count (simple query, with index)
To: Marco Colli <collimarco91@gmail.com>
Cc: pgsql-performance@lists.postgresql.org
References: 
 <CAFvCgN4UijKTYiOF61Tyd+gHvF_oqnMabatS9+DcX+_PK2SHRw@mail.gmail.com>
From: MichaelDBA <MichaelDBA@sqlexec.com>
Message-ID: <02a535cc-ad71-2380-e15a-cf6123fb65d7@sqlexec.com>
Date: Thu, 22 Aug 2019 09:25:36 -0400
User-Agent: Mozilla/5.0 (Windows NT 10.0; WOW64; rv:52.0) Gecko/20100101
 PostboxApp/6.1.18
MIME-Version: 1.0
In-Reply-To: 
 <CAFvCgN4UijKTYiOF61Tyd+gHvF_oqnMabatS9+DcX+_PK2SHRw@mail.gmail.com>
Content-Type: multipart/alternative;
 boundary="------------8D709458648131B9FE380F31"
Content-Language: en-US
Precedence: bulk

This is a multi-part message in MIME format.
--------------8D709458648131B9FE380F31
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit

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
>


--------------8D709458648131B9FE380F31
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 8bit

<html><head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
</head><body bgcolor="#FFFFFF" text="#000000">Hi Marco,<br>
<br>
Since you said approximates would be good enough, there are two ways to 
do that.  Query <span style="font-weight: bold;">pg_class.reltuples</span>
 or <span style="font-weight: bold;">pg_stat_user_tables.n_live_tup</span>. 
 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.<br>
<br>
Regards,<br>
Michael Vitale<br>
<br>
<br>
<span>Marco Colli wrote on 8/22/2019 8:44 AM:</span><br>
<blockquote type="cite" 
cite="mid:CAFvCgN4UijKTYiOF61Tyd+gHvF_oqnMabatS9+DcX+_PK2SHRw@mail.gmail.com">
  <meta http-equiv="content-type" content="text/html; charset=utf-8">
  <div dir="ltr"><div dir="ltr"><div dir="ltr"><div>Hello!</div><div><br></div><div>Any
 help would be greatly appreciated.</div><div>I need to run these simple
 queries on a table with millions of rows:</div><div><br></div><div>```</div><div>SELECT
 COUNT(*) FROM "subscriptions" WHERE "subscriptions"."project_id" = 123;</div><div>```</div><div><br></div><div>```</div><div>SELECT
 COUNT(*) FROM "subscriptions" WHERE "subscriptions"."project_id" = 123 
AND "subscriptions"."trashed_at" IS NULL;</div><div>```</div><div><br></div><div>The
 count result for both queries, for project 123, is about 5M.</div><div><br></div><div>I
 have an index in place on `project_id`, and also another index on 
`(project_id, trashed_at)`:</div><div><br></div><div>```</div><div>"index_subscriptions_on_project_id_and_created_at"
 btree (project_id, created_at DESC)</div><div>"index_subscriptions_on_project_id_and_trashed_at"
 btree (project_id, trashed_at DESC)</div><div>```</div><div><br></div><div>The
 problem is that both queries are extremely slow and take about 17s 
each.</div><div><br></div><div>These are the results of `EXPLAIN 
ANALIZE`:</div><div><br></div><div><br></div><div>```</div><div>      
QUERY PLAN                                                              
                        </div><div>--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------</div><div> Aggregate
  (cost=2068127.29..2068127.30 rows=1 width=0) (actual 
time=17342.420..17342.420 rows=1 loops=1)</div><div>   -&gt;  Bitmap 
Heap Scan on subscriptions  (cost=199573.94..2055635.23 rows=4996823 
width=0) (actual time=1666.409..16855.610 rows=4994254 loops=1)</div><div> 
        Recheck Cond: (project_id = 123)</div><div>         Rows Removed
 by Index Recheck: 23746378</div><div>         Heap Blocks: exact=131205
 lossy=1480411</div><div>         -&gt;  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)</div><div>               Index Cond: (project_id = 123)</div><div> Planning
 time: 0.090 ms</div><div> Execution time: 17344.182 ms</div><div>(9 
rows)</div><div>```</div><div><br></div><div><br></div><div>```</div><div> 
     QUERY PLAN                                                          
                            </div><div>--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------</div><div> Aggregate
  (cost=2047881.69..2047881.70 rows=1 width=0) (actual 
time=17557.218..17557.218 rows=1 loops=1)</div><div>   -&gt;  Bitmap 
Heap Scan on subscriptions  (cost=187953.70..2036810.19 rows=4428599 
width=0) (actual time=1644.966..17078.378 rows=4994130 loops=1)</div><div> 
        Recheck Cond: ((project_id = 123) AND (trashed_at IS NULL))</div><div> 
        Rows Removed by Index Recheck: 23746273</div><div>         Heap 
Blocks: exact=131144 lossy=1480409</div><div>         -&gt;  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)</div><div>              
 Index Cond: ((project_id = 123) AND (trashed_at IS NULL))</div><div> Planning
 time: 0.084 ms</div><div> Execution time: 17558.522 ms</div><div>(9 
rows)</div><div>```</div><div><br></div><div>What is the problem? </div><div>What
 can I do to improve the performance (i.e. count in a few seconds)?<br></div><div><br></div><div>I
 have also tried to increase work_mem from 16MB to 128MB without any 
improvement.</div><div>Even an approximate count would be enough.</div><div>Postgresql
 v9.5</div><div><br></div></div></div></div>
</blockquote>
<br>
</body></html>

--------------8D709458648131B9FE380F31--