Re: Aggregate leads to superfluous projection from the scan

On Fri, Jul 8, 2022 at 10:32 PM Zhihong Yu <zyu(at)yugabyte(dot)com> wrote:

>
>
> On Fri, Jul 8, 2022 at 9:40 AM Zhihong Yu <zyu(at)yugabyte(dot)com> wrote:
>
>> Hi,
>> Here is the query which involves aggregate on a single column:
>>
>>
>> https://dbfiddle.uk/?rdbms=postgres_13&fiddle=44bfd8f6b6b5aad34d00d449c04c5a96
>>
>> 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