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

Поиск
Список
Период
Сортировка
От Axel Andersson
Тема Re: BUG #15608: Index is used for an inherit table but not for thetable with the index
Дата
Msg-id CAJj=OAqZ4Y6R-GXkw3cG5zTc1Sb2VRTDuG1Ey8+2Q0292HK6SA@mail.gmail.com
обсуждение исходный текст
Ответ на Re: BUG #15608: Index is used for an inherit table but not for thetable with the index  (Andres Freund <andres@anarazel.de>)
Список pgsql-bugs
Дерево обсуждения
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@anarazel.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.n.c.andersson@gmail.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

В списке pgsql-bugs по дате отправления:

Предыдущее
От: Andres Freund
Дата:
Сообщение: Re: BUG #15608: Index is used for an inherit table but not for thetable with the index
Следующее
От: Sreeni Survi
Дата:
Сообщение: to_char function returning wrong data