Postgres Pro
Downloads
Home   >   mailing lists

Re: Aggregate leads to superfluous projection from the scan - Mailing list pgsql-hackers

From Ibrar Ahmed
Subject Re: Aggregate leads to superfluous projection from the scan
Date
Msg-id CALtqXTfO-K-Khuos0NU==K-uhutb9c60Guj3EOLoXSmK8LGH9g@mail.gmail.com
Whole thread Raw
In response to Re: Aggregate leads to superfluous projection from the scan  (Zhihong Yu <zyu@yugabyte.com>)
Responses Re: Aggregate leads to superfluous projection from the scan
List pgsql-hackers
Tree view


On Fri, Jul 8, 2022 at 10:32 PM Zhihong Yu <zyu@yugabyte.com> wrote:


On Fri, Jul 8, 2022 at 9:40 AM Zhihong Yu <zyu@yugabyte.com> wrote:
Hi,
Here is the query which involves aggregate on a single column:


As you can see from `Output:`, there are many columns added which are not needed by the query executor.

I wonder if someone has noticed this in the past.
If so, what was the discussion around this topic ?

Thanks
Hi,
With the patch, I was able to get the following output:

 explain (analyze, verbose) /*+ IndexScan(t) */select count(fire_year) from fires t where objectid <= 2000000;
                                                              QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=119.00..119.01 rows=1 width=8) (actual time=9.453..9.453 rows=1 loops=1)
   Output: count(fire_year)
   ->  Index Scan using fires_pkey on public.fires t  (cost=0.00..116.50 rows=1000 width=4) (actual time=9.432..9.432 rows=0 loops=1)
         Output: fire_year
         Index Cond: (t.objectid <= 2000000)
 Planning Time: 52.598 ms
 Execution Time: 13.082 ms

Please pay attention to the column list after `Output:`

Tom:
Can you take a look and let me know what I may have missed ?

Thanks
I give a quick look and I think in case whenever data is extracted from the heap it shows all the columns. Therefore when columns are extracted from the index only it shows the indexed column only. 

postgres=# explain (analyze, verbose) /*+ IndexScan(idx) */select count(fire_year) from fires t where objectid = 20;

                                                                                              QUERY PLAN                                                          

                                    

------------------------------------------------------------------------------------------------------------------------------------------------------------------

------------------------------------

 Aggregate  (cost=8.31..8.32 rows=1 width=8) (actual time=0.029..0.030 rows=1 loops=1)

   Output: count(fire_year)

   ->  Index Scan using fires_pkey on public.fires t  (cost=0.29..8.31 rows=1 width=4) (actual time=0.022..0.023 rows=1 loops=1)

         Output: objectid, fire_name, fire_year, discovery_date, discovery_time, stat_cause_descr, fire_size, fire_size_class, latitude, longitude, state, county,

 discovery_date_j, discovery_date_d

         Index Cond: (t.objectid = 20)

 Planning Time: 0.076 ms

 Execution Time: 0.059 ms

(7 rows)



Index-only.


postgres=# explain (analyze, verbose) /*+ IndexScan(idx) */select count(fire_year) from fires t where fire_year = 20;

                                                          QUERY PLAN                                                           

-------------------------------------------------------------------------------------------------------------------------------

 Aggregate  (cost=8.31..8.32 rows=1 width=8) (actual time=0.026..0.027 rows=1 loops=1)

   Output: count(fire_year)

   ->  Index Only Scan using idx on public.fires t  (cost=0.29..8.31 rows=1 width=4) (actual time=0.023..0.024 rows=0 loops=1)

         Output: fire_year

         Index Cond: (t.fire_year = 20)

         Heap Fetches: 0

 Planning Time: 0.140 ms

 Execution Time: 0.052 ms

(8 rows)



Index Scans

------------

postgres=# explain (analyze, verbose) select count(fire_year) from fires t where objectid = 20;

 Aggregate  (cost=8.31..8.32 rows=1 width=8) (actual time=0.030..0.031 rows=1 loops=1)

   Output: count(fire_year)

   ->  Index Scan using fires_pkey on public.fires t  (cost=0.29..8.31 rows=1 width=4) (actual time=0.021..0.023 rows=1 loops=1)

         Output: objectid, fire_name, fire_year, discovery_date, discovery_time, stat_cause_descr, fire_size, fire_size_class, latitude, longitude, state, county,

 discovery_date_j, discovery_date_d

         Index Cond: (t.objectid = 20)

 Planning Time: 0.204 ms

 Execution Time: 0.072 ms

(7 rows)



Seq scans.

----------


postgres=# explain (analyze, verbose) select count(fire_year) from fires t;

                                     

                                    

 Aggregate  (cost=1791.00..1791.01 rows=1 width=8) (actual time=13.172..13.174 rows=1 loops=1)

   Output: count(fire_year)

   ->  Seq Scan on public.fires t  (cost=0.00..1541.00 rows=100000 width=4) (actual time=0.007..6.500 rows=100000 loops=1)

         Output: objectid, fire_name, fire_year, discovery_date, discovery_time, stat_cause_descr, fire_size, fire_size_class, latitude, longitude, state, county,

 discovery_date_j, discovery_date_d

 Planning Time: 0.094 ms

 Execution Time: 13.201 ms

(6 rows)



--
Ibrar Ahmed

pgsql-hackers by date:

Previous
From: Jacob Champion
Date:
Subject: Re: [PATCH] Log details for client certificate failures
Next
From: Alexander Korotkov
Date:
Subject: Re: Add red-black tree missing comparison searches