How can I check available space of an index's pages in PostgreSQL?





.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}







0















For tables, I can simply use pg_freespace to check available spaces like below:



postgres=> SELECT count(*),avg(1000*avail/8192)/10 FROM pg_freespace('tablename');
count | ?column?
---------+------------------------
1978140 | 1.00397105361602313000


But for indexes, it does not work.



postgres=> SELECT count(*),avg(1000*avail/8192)/10 FROM pg_freespace('tablename_pkey');
count | ?column?
---------+----------------------------
1224867 | 0.000000000000000000000000


Additionally, this is the result of same queries after VACUUM FULL:



postgres=> SELECT count(*),avg(1000*avail/8192)/10 FROM pg_freespace('tablename');
count | ?column?
---------+----------------------------
1833413 | 0.000000000000000000000000
postgres=> SELECT count(*),avg(1000*avail/8192)/10 FROM pg_freespace('tablename_pkey');
count | ?column?
--------+----------------------------
836994 | 0.000000000000000000000000








share





























    0















    For tables, I can simply use pg_freespace to check available spaces like below:



    postgres=> SELECT count(*),avg(1000*avail/8192)/10 FROM pg_freespace('tablename');
    count | ?column?
    ---------+------------------------
    1978140 | 1.00397105361602313000


    But for indexes, it does not work.



    postgres=> SELECT count(*),avg(1000*avail/8192)/10 FROM pg_freespace('tablename_pkey');
    count | ?column?
    ---------+----------------------------
    1224867 | 0.000000000000000000000000


    Additionally, this is the result of same queries after VACUUM FULL:



    postgres=> SELECT count(*),avg(1000*avail/8192)/10 FROM pg_freespace('tablename');
    count | ?column?
    ---------+----------------------------
    1833413 | 0.000000000000000000000000
    postgres=> SELECT count(*),avg(1000*avail/8192)/10 FROM pg_freespace('tablename_pkey');
    count | ?column?
    --------+----------------------------
    836994 | 0.000000000000000000000000








    share

























      0












      0








      0








      For tables, I can simply use pg_freespace to check available spaces like below:



      postgres=> SELECT count(*),avg(1000*avail/8192)/10 FROM pg_freespace('tablename');
      count | ?column?
      ---------+------------------------
      1978140 | 1.00397105361602313000


      But for indexes, it does not work.



      postgres=> SELECT count(*),avg(1000*avail/8192)/10 FROM pg_freespace('tablename_pkey');
      count | ?column?
      ---------+----------------------------
      1224867 | 0.000000000000000000000000


      Additionally, this is the result of same queries after VACUUM FULL:



      postgres=> SELECT count(*),avg(1000*avail/8192)/10 FROM pg_freespace('tablename');
      count | ?column?
      ---------+----------------------------
      1833413 | 0.000000000000000000000000
      postgres=> SELECT count(*),avg(1000*avail/8192)/10 FROM pg_freespace('tablename_pkey');
      count | ?column?
      --------+----------------------------
      836994 | 0.000000000000000000000000








      share














      For tables, I can simply use pg_freespace to check available spaces like below:



      postgres=> SELECT count(*),avg(1000*avail/8192)/10 FROM pg_freespace('tablename');
      count | ?column?
      ---------+------------------------
      1978140 | 1.00397105361602313000


      But for indexes, it does not work.



      postgres=> SELECT count(*),avg(1000*avail/8192)/10 FROM pg_freespace('tablename_pkey');
      count | ?column?
      ---------+----------------------------
      1224867 | 0.000000000000000000000000


      Additionally, this is the result of same queries after VACUUM FULL:



      postgres=> SELECT count(*),avg(1000*avail/8192)/10 FROM pg_freespace('tablename');
      count | ?column?
      ---------+----------------------------
      1833413 | 0.000000000000000000000000
      postgres=> SELECT count(*),avg(1000*avail/8192)/10 FROM pg_freespace('tablename_pkey');
      count | ?column?
      --------+----------------------------
      836994 | 0.000000000000000000000000






      postgresql-10 vacuum





      share












      share










      share



      share










      asked 8 mins ago









      Bill GreensBill Greens

      183




      183






















          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%2f234151%2fhow-can-i-check-available-space-of-an-indexs-pages-in-postgresql%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%2f234151%2fhow-can-i-check-available-space-of-an-indexs-pages-in-postgresql%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