Xrhrft

My Postgresql database (postgres version 11.1 and postgis version 2.3) consists of hundreds of tables and each table contains 10 of millions of rows. Data is divided by month and then each month table (like public.table_2018_1) inherits a year table INHERITS (public.table_2018). Each month table have indexes (the year table have 0 index directly connected to it).

The query is a little bit long but it looks something like this:

with pl as (SELECT ST_GeomFromText('LINESTRING(10.9 45.9, 10.8 45.8)', 4326) as line

), segments as (select ss.col1, ss.col2, at.col3, at.thrid_id

    FROM table_2018_1 ss

    JOIN another_table at on at.rowid=ss.another_id

    where st_intersects(linestring_column, (select line from pl))

), third_info as(select col4, col5

    FROM other_schema.table3

)

SELECT col1, col2, col3, col4, col5

FROM segments ss

left outer JOIN third_info sd on ss.third_id=sd.rowid

Which works, but it takes approximate 30sec to run, but if I change table_2018_1 (in the segments with block) to the "inherit" table_2018 the query ready in under 3 sec (with ~12 times the amount of data).

So I used EXPLAIN before the two questions and found that the planner made a huge difference in the planning between the two queries. With the inherit table_2018:

• It starts with a Merge Left Join (Merge Cond: ss.third_id=sd.rowid)


• In the CTE segments it looks something like this:

Hash Cond: (ss_1.annother_id = at.rowid)

->  Append  (cost=0.00..116480.30 rows=9596 width=66)

      ->  Seq Scan on table_2018 ss_1  (cost=0.00..0.00 rows=1 width=66)

            Filter: ((linestring_column && $1) AND _st_intersects(linestring_column , $1))

      ->  Bitmap Heap Scan on table_2018_1 ss_2  (cost=88.78..8727.97 rows=719 width=66)

            Recheck Cond: (linestring_column && $1)

            Filter: _st_intersects(linestring_column , $1)

            ->  Bitmap Index Scan on table_2018_1_gist_index  (cost=0.00..88.60 rows=2158 width=0)

                  Index Cond: (linestring_column && $1)

      ->  Bitmap Heap Scan on table_2018_2 ss_3  (cost=78.88..7713.93 rows=636 width=66)

and then continues for all 12 months..

When I look at the EXPLAIN query for table_2018_1:

• It starts with a Merge Right Join (Merge Cond: ss.third_id=sd.rowid)


• In the CTE segments it looks something like this:

Hash Cond: (ss_1.another_id = at.rowid)

->  Seq Scan on table_2018_1 ss_1  (cost=0.00..5927612.32 rows=7192677 width=66)

    Filter: st_intersects(linestring_column, $1)

->  Hash  (cost=7964.80..7964.80 rows=298680 width=26)

I understand that the big difference between the run time is that when I use the inherit table it uses the gist index, the question is why it doesn't use it for the month table and how do I force it to use it? (Without something like set enable_seqscan=false?)