Re: BUG #15608: Index is used for an inherit table but not for the table with the index

I had the following query:
"""
EXPLAIN (ANALYZE, BUFFERS)
with pl as (SELECT ST_GeomFromText('LINESTRING(10.796171 55.761263,
10.820721 55.762100)', 4326) as line
)
select col1, col2. col3
FROM table_2018_1 ss
where st_intersects(linestring_column, (select line from pl))
"""
Where table_2018_1 have column, col1, col2, col3 and a linestring_column,
it also inherits "table_2018" (which 11 other table also inherits), the
query results in:
"""
Seq Scan on table_2018_1 ss (cost=0.03..4059060.91 rows=4880296 width=66)
(actual time=819.088..25358.121 rows=160 loops=1)
Filter: st_intersects( linestring_column , $1)
Rows Removed by Filter: 14640728
Buffers: shared hit=4 read=252426 written=37319
CTE pl
-> Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.001..0.001
rows=1 loops=1)
InitPlan 2 (returns $1)
-> CTE Scan on pl (cost=0.00..0.02 rows=1 width=32) (actual
time=0.003..0.003 rows=1 loops=1)
Planning Time: 1.159 ms
Execution Time: 25358.181 ms
"""
However if I change from "table_2018_1" to "table_2018" the query results
looks like this:
"""
Append (cost=0.03..80189.59 rows=6561 width=66) (actual
time=5.843..370.103 rows=725 loops=1)
Buffers: shared hit=105 read=3013
CTE pl
-> Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.001..0.001
rows=1 loops=1)
InitPlan 2 (returns $1)
-> CTE Scan on pl (cost=0.00..0.02 rows=1 width=32) (actual
time=0.002..0.002 rows=1 loops=1)
-> Seq Scan on table_2018 ss (cost=0.00..0.00 rows=1 width=50) (actual
time=0.001..0.001 rows=0 loops=1)
Filter: ((linestring_column && $1) AND
_st_intersects(linestring_column, $1))
-> Bitmap Heap Scan on table_2018_1 ss_1 (cost=71.52..5962.72 rows=488
width=66) (actual time=5.841..28.205 rows=25 loops=1)
Recheck Cond: (linestring_column && $1)
Filter: _st_intersects(linestring_column, $1)
Rows Removed by Filter: 106
Heap Blocks: exact=131
Buffers: shared read=151
-> Bitmap Index Scan on table_2018_1_linestring_column_gist
(cost=0.00..71.40 rows=1464 width=0) (actual time=4.728..4.728 rows=131
loops=1)
Index Cond: (linestring_column && $1)
Buffers: shared read=20
-> Bitmap Heap Scan on table_2018_2 ss_2 (cost=61.89..5089.43 rows=416
width=66) (actual time=6.563..16.154 rows=10 loops=1)
Recheck Cond: (linestring_column && $1)
Filter: _st_intersects(linestring_column, $1)
Rows Removed by Filter: 47
Heap Blocks: exact=57
Buffers: shared read=71
-> Bitmap Index Scan on table_2018_2_linestring_column_gist
(cost=0.00..61.78 rows=1249 width=0) (actual time=4.606..4.607 rows=57
loops=1)
Index Cond: (linestring_column && $1)
Buffers: shared read=14
-> Bitmap Heap Scan on table_2018_3 ss_3 (cost=75.90..6168.81 rows=505
width=66) (actual time=5.458..20.508 rows=15 loops=1)
Recheck Cond: (linestring_column && $1)
Filter: _st_intersects(linestring_column, $1)
Rows Removed by Filter: 80
Heap Blocks: exact=95
Buffers: shared read=120
-> Bitmap Index Scan on table_2018_3_linestring_column_gist
(cost=0.00..75.77 rows=1514 width=0) (actual time=4.739..4.739 rows=95
loops=1)
Index Cond: (linestring_column && $1)
Buffers: shared read=25
-> Bitmap Heap Scan on table_2018_4 ss_4 (cost=76.34..6403.16 rows=524
width=66) (actual time=5.345..35.295 rows=39 loops=1)
Recheck Cond: (linestring_column && $1)
Filter: _st_intersects(linestring_column, $1)
Rows Removed by Filter: 169
Heap Blocks: exact=208
Buffers: shared read=227
-> Bitmap Index Scan on table_2018_4_linestring_column_gist
(cost=0.00..76.21 rows=1572 width=0) (actual time=4.063..4.063 rows=208
loops=1)
Index Cond: (linestring_column && $1)
Buffers: shared read=19
-> Bitmap Heap Scan on table_2018_5 ss_5 (cost=90.61..7619.96 rows=624
width=66) (actual time=11.115..80.487 rows=129 loops=1)
Recheck Cond: (linestring_column && $1)
Filter: _st_intersects(linestring_column, $1)
Rows Removed by Filter: 348
Heap Blocks: exact=477
Buffers: shared read=542
-> Bitmap Index Scan on table_2018_5_linestring_column_gist
(cost=0.00..90.45 rows=1871 width=0) (actual time=10.142..10.142 rows=477
loops=1)
Index Cond: (linestring_column && $1)
Buffers: shared read=65
-> Bitmap Heap Scan on table_2018_6 ss_6 (cost=95.36..8024.15 rows=657
width=66) (actual time=8.902..56.589 rows=120 loops=1)
Recheck Cond: (linestring_column && $1)
Filter: _st_intersects(linestring_column, $1)
Rows Removed by Filter: 233
Heap Blocks: exact=353
Buffers: shared read=402
-> Bitmap Index Scan on table_2018_6_linestring_column_gist
(cost=0.00..95.19 rows=1970 width=0) (actual time=7.682..7.682 rows=353
loops=1)
Index Cond: (linestring_column && $1)
Buffers: shared read=49
-> Bitmap Heap Scan on table_2018_7 ss_7 (cost=99.93..8335.22 rows=682
width=66) (actual time=11.874..86.257 rows=160 loops=1)
Recheck Cond: (linestring_column && $1)
Filter: _st_intersects(linestring_column, $1)
Rows Removed by Filter: 346
Heap Blocks: exact=505
Buffers: shared read=580
-> Bitmap Index Scan on table_2018_7_linestring_column_gist
(cost=0.00..99.76 rows=2046 width=0) (actual time=11.468..11.468 rows=506
loops=1)
Index Cond: (linestring_column && $1)
Buffers: shared read=75
-> Bitmap Heap Scan on table_2018_8 ss_8 (cost=80.60..6548.69 rows=536
width=66) (actual time=4.156..28.094 rows=45 loops=1)
Recheck Cond: (linestring_column && $1)
Filter: _st_intersects(linestring_column, $1)
Rows Removed by Filter: 154
Heap Blocks: exact=199
Buffers: shared read=222
-> Bitmap Index Scan on table_2018_8_linestring_column_gist
(cost=0.00..80.47 rows=1607 width=0) (actual time=3.701..3.701 rows=199
loops=1)
Index Cond: (linestring_column && $1)
Buffers: shared read=23
-> Bitmap Heap Scan on table_2018_9 ss_9 (cost=81.02..6770.91 rows=554
width=66) (actual time=3.672..4.708 rows=58 loops=1)
Recheck Cond: (linestring_column && $1)
Filter: _st_intersects(linestring_column, $1)
Rows Removed by Filter: 113
Heap Blocks: exact=171
Buffers: shared read=193
-> Bitmap Index Scan on table_2018_9_linestring_column_gist
(cost=0.00..80.88 rows=1662 width=0) (actual time=3.569..3.569 rows=171
loops=1)
Index Cond: (linestring_column && $1)
Buffers: shared read=22
-> Bitmap Heap Scan on table_2018_10 ss_10 (cost=95.07..7870.49
rows=644 width=66) (actual time=4.228..5.654 rows=61 loops=1)
Recheck Cond: (linestring_column && $1)
Filter: _st_intersects(linestring_column, $1)
Rows Removed by Filter: 189
Heap Blocks: exact=250
Buffers: shared read=280
-> Bitmap Index Scan on table_2018_10_linestring_column_gist
(cost=0.00..94.91 rows=1932 width=0) (actual time=4.088..4.088 rows=250
loops=1)
Index Cond: (linestring_column && $1)
Buffers: shared read=30
-> Bitmap Heap Scan on table_2018_11 ss_11 (cost=80.95..6734.54
rows=551 width=66) (actual time=4.395..5.688 rows=57 loops=1)
Recheck Cond: (linestring_column && $1)
Filter: _st_intersects(linestring_column, $1)
Rows Removed by Filter: 206
Heap Blocks: exact=263
Buffers: shared hit=79 read=210
-> Bitmap Index Scan on table_2018_11_linestring_column_gist
(cost=0.00..80.81 rows=1653 width=0) (actual time=4.329..4.329 rows=263
loops=1)
Index Cond: (linestring_column && $1)
Buffers: shared read=26
-> Bitmap Heap Scan on table_2018_12 ss_12 (cost=57.03..4628.67
rows=379 width=66) (actual time=2.280..2.351 rows=6 loops=1)
Recheck Cond: (linestring_column && $1)
Filter: _st_intersects(linestring_column, $1)
Rows Removed by Filter: 20
Heap Blocks: exact=26
Buffers: shared hit=26 read=15
-> Bitmap Index Scan on table_2018_12_linestring_column_gist
(cost=0.00..56.94 rows=1136 width=0) (actual time=2.245..2.245 rows=26
loops=1)
Index Cond: (linestring_column && $1)
Buffers: shared read=15
Planning Time: 1.693 ms
Execution Time: 370.307 ms
"""
I tried to "vacuum full verbose analyze", "reindex table_2018_1",
"set_default_statistic =1000"

I also removed the inheritance to "table_2018" but still the gist index
isn't evaluated when I run the query on "table_2018_1"..

Any suggestions? Or information missing?

On Mon, 4 Feb 2019 at 09:48, Andres Freund <andres(at)anarazel(dot)de> wrote:

> Hi,
>
> On 2019-01-28 10:00:39 +0000, PG Bug reporting form wrote:
> > The following bug has been logged on the website:
> >
> > Bug reference: 15608
> > Logged by: Axel Hörteborn
> > Email address: axel(dot)n(dot)c(dot)andersson(at)gmail(dot)com
> > PostgreSQL version: 11.1
> > Operating system: Windows 10 x64
> > Description:
> >
> > I tried to ask a question on Stackoverflow but it seems to be a bug:
> > Hopefully is all the details required in the Stackoverflow question, in
> > short I have a "table_2017_1" which Inherits "table_2017", so no data or
> > index are stored directly in "table_2017". When I run a question on
> > table_2017 it uses the index on table_2017_1 but when I run the exact
> same
> > question on "table_2017_1" it doesn't evaluate the index and perform a
> > sequence scan instead.
> >
> >
> https://stackoverflow.com/questions/54362324/postgresql-index-is-used-for-an-inherit-table-but-not-for-the-table-with-the-i
>
> As discussed on IRC, it'd be helpful to include all details via email,
> without referencing stackoverflow etc.
>
> Greetings,
>
> Andres Freund
>