Postgresql - Usage of index on an inherit table but not for the original table (with the index)












0















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?)









share



























    0















    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?)









    share

























      0












      0








      0








      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?)









      share














      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?)







      postgresql index postgis





      share












      share










      share



      share










      asked 4 mins ago









      axel_andeaxel_ande

      1083




      1083






















          0






          active

          oldest

          votes











          Your Answer








          StackExchange.ready(function() {
          var channelOptions = {
          tags: "".split(" "),
          id: "182"
          };
          initTagRenderer("".split(" "), "".split(" "), channelOptions);

          StackExchange.using("externalEditor", function() {
          // Have to fire editor after snippets, if snippets enabled
          if (StackExchange.settings.snippets.snippetsEnabled) {
          StackExchange.using("snippets", function() {
          createEditor();
          });
          }
          else {
          createEditor();
          }
          });

          function createEditor() {
          StackExchange.prepareEditor({
          heartbeatType: 'answer',
          autoActivateHeartbeat: false,
          convertImagesToLinks: false,
          noModals: true,
          showLowRepImageUploadWarning: true,
          reputationToPostImages: null,
          bindNavPrevention: true,
          postfix: "",
          imageUploader: {
          brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
          contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
          allowUrls: true
          },
          onDemand: true,
          discardSelector: ".discard-answer"
          ,immediatelyShowMarkdownHelp:true
          });


          }
          });














          draft saved

          draft discarded


















          StackExchange.ready(
          function () {
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f227935%2fpostgresql-usage-of-index-on-an-inherit-table-but-not-for-the-original-table%23new-answer', 'question_page');
          }
          );

          Post as a guest















          Required, but never shown

























          0






          active

          oldest

          votes








          0






          active

          oldest

          votes









          active

          oldest

          votes






          active

          oldest

          votes
















          draft saved

          draft discarded




















































          Thanks for contributing an answer to Database Administrators Stack Exchange!


          • Please be sure to answer the question. Provide details and share your research!

          But avoid



          • Asking for help, clarification, or responding to other answers.

          • Making statements based on opinion; back them up with references or personal experience.


          To learn more, see our tips on writing great answers.




          draft saved


          draft discarded














          StackExchange.ready(
          function () {
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f227935%2fpostgresql-usage-of-index-on-an-inherit-table-but-not-for-the-original-table%23new-answer', 'question_page');
          }
          );

          Post as a guest















          Required, but never shown





















































          Required, but never shown














          Required, but never shown












          Required, but never shown







          Required, but never shown

































          Required, but never shown














          Required, but never shown












          Required, but never shown







          Required, but never shown







          Popular posts from this blog

          Liste der Baudenkmale in Friedland (Mecklenburg)

          Single-Malt-Whisky

          Czorneboh