PostgreSQL › PostgreSQL - bugs

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

_‹ Previous Topic Next Topic _›

Classic

List

Threaded

3 messages Options

apt.postgresql.org Repository Update

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

The following bug has been logged on the website:

Bug reference: 15608
Logged by: Axel Hörteborn
Email address: [hidden email]
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

Andres Freund

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

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: [hidden email]
> 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

Axel Andersson

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 <[hidden email]> 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: [hidden email]
> 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