Re: pg11: Query using index, but only for half the partitions

Thanks Andreas,

But I don't think that that's what's happening.

Take this example line:
-> Seq Scan on snap_20200328 s_23 (cost=0.00..51.73
rows=95 width=12) (actual time=0.007..0.225 rows=95 loops=1)
Filter: ((instance_id)::text = 'test01'::text)
Rows Removed by Filter: 1723

There's no question that this is more expensive than just reading the 95
rows from the index directly and returning them - table access is not
required for this query.

The index fully satisfies the query's columns, as evident in the output:

-> Index Only Scan using
snap_20200325_start_time_snap_id_instance_id_idx on snap_20200325 s_20
(cost=0.28..74.80 rows=95 width=12) (actual time=0.011..0.113 rows=95
loops=1)
Index Cond: (instance_id = 'test01'::text)
---------> Heap Fetches: 0

That is, unless it doesn't consider that fact when costing?

On Thu, Apr 23, 2020 at 5:01 PM Andreas Kretschmer <andreas(at)a-kretschmer(dot)de>
wrote:

>
>
> Am 23.04.20 um 10:13 schrieb Stefan Knecht:
> > Seq Scan on snap_20200225 s (cost=0.00..1.19 rows=1 width=12)
>
> the partition is very small, so it's cheaper to scan only the table (one
> block) than index + table (1 + 1 block).
>
>
> Regards, Andreas
>
> --
> 2ndQuadrant - The PostgreSQL Support Company.
> www.2ndQuadrant.com
>
>
>
>

--
//
zztat - The Next-Gen Oracle Performance Monitoring and Reaction Framework!
Visit us at zztat.net | @zztat_oracle | fb.me/zztat | zztat.net/blog/