Content-Type: multipart/alternative;
 boundary="------------d3Q0iF8fqTe4Y9amctrn0hyV"
Message-ID: <d56ab3ae-baee-c93d-fc0d-b55356b2729a@gmail.com>
Date: Tue, 12 Jul 2022 22:54:50 -0400
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:91.0) Gecko/20100101
 Thunderbird/91.11.0
Subject: Re: [EXT] Re: Improve "select count(*)" query - takes more than 30
 mins for some large tables
Content-Language: en-US
To: MichaelDBA Vitale <michaeldba@sqlexec.com>,
 "Pierson Patricia L (Contractor)" <Patricia.L.Pierson@irs.gov>
Cc: "pgsql-admin@lists.postgresql.org" <pgsql-admin@lists.postgresql.org>
References: 
 <CACxU--UJY4sD2SjW8V3W0oAPuuTzE3fAvBzTaej-BmUUNJxN_Q@mail.gmail.com>
 <1B1927BA-F7DE-40E8-8F67-BCC001CD4C6B@gmail.com>
 <1eb233ff-90f0-529b-f0cc-fa982f668a8f@gmail.com>
 <1221536526.20277.1657636277921@email.ionos.com>
 <CALcG2D+JJMK13U3qV_pn8np+m2urGJ7iyj_hB0Mt9XfM8GU-vw@mail.gmail.com>
 <SJ0PR09MB6061D637A2A7988D19DC506FC7869@SJ0PR09MB6061.namprd09.prod.outlook.com>
 <1037158693.49441.1657650345834@email.ionos.com>
From: Mladen Gogala <gogala.mladen@gmail.com>
In-Reply-To: <1037158693.49441.1657650345834@email.ionos.com>
Archived-At: 
 <https://www.postgresql.org/message-id/d56ab3ae-baee-c93d-fc0d-b55356b2729a%40gmail.com>
Precedence: bulk

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

On 7/12/22 14:25, MichaelDBA Vitale wrote:
> That is not true: doing the select on the primary key will still 
> result in a table scan, not an index scan.  The heap always gets 
> accessed for select counts.
>
> Regards,
> Michael Vitale

Well, not always:

mgogala=# explain select count(*) from emp;
                         QUERY PLAN
----------------------------------------------------------
  Aggregate  (cost=1.18..1.19 rows=1 width=8)
    ->  Seq Scan on emp  (cost=0.00..1.14 rows=14 width=0)
(2 rows)

That is the classic "select count(*)".  Now, let's see PK:

mgogala=# \d emp
                            Table "mgogala.emp"
   Column  |            Type             | Collation | Nullable | Default
----------+-----------------------------+-----------+----------+---------
  empno    | smallint                    |           | not null |
  ename    | character varying(10)       |           |          |
  job      | character varying(9)        |           |          |
  mgr      | smallint                    |           |          |
  hiredate | timestamp without time zone |           |          |
  sal      | double precision            |           |          |
  comm     | double precision            |           |          |
  deptno   | smallint                    |           |          |
Indexes:
     "emp_pkey" PRIMARY KEY, btree (empno)
Foreign-key constraints:
     "fk_deptno" FOREIGN KEY (deptno) REFERENCES dept(deptno)

Now, let's do scan on "count(empno)":

mgogala=# explain select count(empno) from emp;
                         QUERY PLAN
----------------------------------------------------------
  Aggregate  (cost=1.18..1.19 rows=1 width=8)
    ->  Seq Scan on emp  (cost=0.00..1.14 rows=14 width=2)
(2 rows)

So far, so good. Sequential scan, despite the existence of the NOT NULL 
primary key. Now comes the twist:

mgogala=# set enable_seqscan=off;
SET
mgogala=# explain select count(empno) from emp;
                                    QUERY PLAN

--------------------------------------------------------------------------------
-
  Aggregate  (cost=12.38..12.39 rows=1 width=8)
    ->  Index Only Scan using emp_pkey on emp (cost=0.14..12.35 rows=14 
width=2)
(2 rows)

Voila, we've got index only "count" scan. So, in some cases Postgres 
does do index only scan. What is surprising is that pg_hint_plan doesn't 
produce the desired results:

mgogala=# explain /*+ IndexScan(emp emp_pkey) */ select count(empno) 
from emp;
                                   QUERY PLAN
-------------------------------------------------------------------------------
  Aggregate  (cost=10000000001.17..10000000001.18 rows=1 width=8)
    ->  Seq Scan on emp  (cost=10000000000.00..10000000001.14 rows=14 
width=2)
  JIT:
    Functions: 3
    Options: Inlining true, Optimization true, Expressions true, 
Deforming true
(5 rows)

This is strange in more ways than one:

 1. The hint didn't work, Postgres still performed sequential scan
 2. JIT got involved for the reasons that are completely unclear.
 3. The cost is enormous, despite the fact the the "emp" table has only
    14 rows and is fully contained in a single block.

However, if we do GUC hint, the result behaves as expected:

mgogala=# explain /*+ set(enable_seqscan off) */ select count(empno) 
from emp e;
                                     QUERY PLAN

--------------------------------------------------------------------------------
---
  Aggregate  (cost=12.38..12.39 rows=1 width=8)
    ->  Index Only Scan using emp_pkey on emp e (cost=0.14..12.35 
rows=14 width=2)
(2 rows)

So, it is possible to force index only scan. However, from purely 
theoretic point of view, the speed difference is questionable. Indexes 
are stored differently than tables, they have branch blocks and data 
bocks with an ample free space in the data blocks to prevent block 
splits and index level increase, with mandatory re-balancing. Indexes 
are actually quite large structures, sometimes even larger than the 
underlying tables. In general, going after the index-only scan doesn't 
look like a good strategy.

After all this work, I also have a question: does PostgreSQL read index 
blocks in batches or it does single block scans? Some other databases 
have 2 events: "db file scattered read" for sequential scan (called 
"full table scan" in that specific vernacular) and "db file sequential 
read" for index scans. Those database only do single block reads from 
indexes while the tables can be read using batches of blocks. Does 
PostgreSQL do the same thing?

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

--------------d3Q0iF8fqTe4Y9amctrn0hyV
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>
    <div class="moz-cite-prefix">On 7/12/22 14:25, MichaelDBA Vitale
      wrote:<br>
    </div>
    <blockquote type="cite"
      cite="mid:1037158693.49441.1657650345834@email.ionos.com">
      <div> That is not true: doing the select on the primary key will
        still result in a table scan, not an index scan.  The heap
        always gets accessed for select counts. </div>
      <div class="default-style"> <br>
      </div>
      <div class="default-style"> Regards, </div>
      <div class="default-style"> Michael Vitale </div>
    </blockquote>
    <p>Well, not always:</p>
    <p><font face="monospace" color="blue">mgogala=# explain select
        count(*) from emp;<br>
                                QUERY PLAN                        <br>
        ----------------------------------------------------------<br>
         Aggregate  (cost=1.18..1.19 rows=1 width=8)<br>
           -&gt;  Seq Scan on emp  (cost=0.00..1.14 rows=14 width=0)<br>
        (2 rows)</font><br>
    </p>
    <p>That is the classic "select count(*)".  Now, let's see PK:</p>
    <p><font face="monospace" color="blue">mgogala=# \d emp<br>
                                   Table "mgogala.emp"<br>
          Column  |            Type             | Collation | Nullable |
        Default <br>
----------+-----------------------------+-----------+----------+---------<br>
         empno    | smallint                    |           | not null |
        <br>
         ename    | character varying(10)       |           |          |
        <br>
         job      | character varying(9)        |           |          |
        <br>
         mgr      | smallint                    |           |          |
        <br>
         hiredate | timestamp without time zone |           |          |
        <br>
         sal      | double precision            |           |          |
        <br>
         comm     | double precision            |           |          |
        <br>
         deptno   | smallint                    |           |          |
        <br>
        Indexes:<br>
            "emp_pkey" PRIMARY KEY, btree (empno)<br>
        Foreign-key constraints:<br>
            "fk_deptno" FOREIGN KEY (deptno) REFERENCES dept(deptno)</font><br>
      <br>
      Now, let's do scan on "count(empno)":</p>
    <p><font face="monospace" color="blue">mgogala=# explain select
        count(empno) from emp;<br>
                                QUERY PLAN                        <br>
        ----------------------------------------------------------<br>
         Aggregate  (cost=1.18..1.19 rows=1 width=8)<br>
           -&gt;  Seq Scan on emp  (cost=0.00..1.14 rows=14 width=2)<br>
        (2 rows)</font><br>
    </p>
    <p>So far, so good. Sequential scan, despite the existence of the
      NOT NULL primary key. Now comes the twist:<br>
    </p>
    <p><font face="monospace" color="blue">mgogala=# set
        enable_seqscan=off;<br>
        SET<br>
        mgogala=# explain select count(empno) from emp;<br>
                                           QUERY
        PLAN                                   <br>
         <br>
--------------------------------------------------------------------------------<br>
        -<br>
         Aggregate  (cost=12.38..12.39 rows=1 width=8)<br>
           -&gt;  Index Only Scan using emp_pkey on emp 
        (cost=0.14..12.35 rows=14 width=2)<br>
        (2 rows)</font><br>
    </p>
    <p>Voila, we've got index only "count" scan. So, in some cases
      Postgres does do index only scan. What is surprising is that
      pg_hint_plan doesn't produce the desired results:</p>
    <p><font face="monospace" color="blue">mgogala=# explain /*+
        IndexScan(emp emp_pkey) */ select count(empno) from emp;<br>
                                          QUERY
        PLAN                                   <br>
-------------------------------------------------------------------------------<br>
         Aggregate  (cost=10000000001.17..10000000001.18 rows=1 width=8)<br>
           -&gt;  Seq Scan on emp  (cost=10000000000.00..10000000001.14
        rows=14 width=2)<br>
         JIT:<br>
           Functions: 3<br>
           Options: Inlining true, Optimization true, Expressions true,
        Deforming true<br>
        (5 rows)</font><br>
    </p>
    <p>This is strange in more ways than one:</p>
    <ol>
      <li>The hint didn't work, Postgres still performed sequential scan</li>
      <li>JIT got involved for the reasons that are completely unclear.</li>
      <li>The cost is enormous, despite the fact the the "emp" table has
        only 14 rows and is fully contained in a single block.<br>
      </li>
    </ol>
    <p>However, if we do GUC hint, the result behaves as expected:</p>
    <p><font face="monospace" color="blue">mgogala=# explain /*+
        set(enable_seqscan off) */ select count(empno) from emp e;<br>
                                            QUERY
        PLAN                                  <br>
           <br>
--------------------------------------------------------------------------------<br>
        ---<br>
         Aggregate  (cost=12.38..12.39 rows=1 width=8)<br>
           -&gt;  Index Only Scan using emp_pkey on emp e 
        (cost=0.14..12.35 rows=14 width=2)<br>
        (2 rows)</font><br>
    </p>
    <p>So, it is possible to force index only scan. However, from purely
      theoretic point of view, the speed difference is questionable.
      Indexes are stored differently than tables, they have branch
      blocks and data bocks with an ample free space in the data blocks
      to prevent block splits and index level increase, with mandatory
      re-balancing. Indexes are actually quite large structures,
      sometimes even larger than the underlying tables. In general,
      going after the index-only scan doesn't look like a good strategy.</p>
    <p>After all this work, I also have a question: does PostgreSQL read
      index blocks in batches or it does single block scans? Some other
      databases have 2 events: "db file scattered read" for sequential
      scan (called "full table scan" in that specific vernacular) and
      "db file sequential read" for index scans. Those database only do
      single block reads from indexes while the tables can be read using
      batches of blocks. Does PostgreSQL do the same thing?<br>
    </p>
    <pre class="moz-signature" cols="72">-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
<a class="moz-txt-link-freetext" href="https://dbwhisperer.wordpress.com">https://dbwhisperer.wordpress.com</a>
</pre>
  </body>
</html>

--------------d3Q0iF8fqTe4Y9amctrn0hyV--