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
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
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@xxxxxxxxxxxxxxx> 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!