Re: rows estimate in explain analyze for the BRIN index - Mailing list pgsql-hackers

Oleksii Kliukin <alexk@hintbits.com> writes:
>> On 30 Dec 2015, at 17:02, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Another idea would be to use the heap's row density as calculated
>> by the last ANALYZE (ie, reltuples/relpages), with a fallback to 100
>> if relpages=0.  This'd only be convenient if the bitmap scan node has
>> the parent heap rel open, which it might not.

> +1

Any objections to the attached?

            regards, tom lane

diff --git a/src/backend/access/brin/brin.c b/src/backend/access/brin/brin.c
index 2622a7e..64bf788 100644
*** a/src/backend/access/brin/brin.c
--- b/src/backend/access/brin/brin.c
*************** bringetbitmap(PG_FUNCTION_ARGS)
*** 279,286 ****
      Relation    heapRel;
      BrinOpaque *opaque;
      BlockNumber nblocks;
      BlockNumber heapBlk;
!     int            totalpages = 0;
      FmgrInfo   *consistentFn;
      MemoryContext oldcxt;
      MemoryContext perRangeCxt;
--- 279,287 ----
      Relation    heapRel;
      BrinOpaque *opaque;
      BlockNumber nblocks;
+     int            heap_tuples_per_page;
      BlockNumber heapBlk;
!     int64        totalpages = 0;
      FmgrInfo   *consistentFn;
      MemoryContext oldcxt;
      MemoryContext perRangeCxt;
*************** bringetbitmap(PG_FUNCTION_ARGS)
*** 291,301 ****

      /*
       * We need to know the size of the table so that we know how long to
!      * iterate on the revmap.
       */
      heapOid = IndexGetRelation(RelationGetRelid(idxRel), false);
      heapRel = heap_open(heapOid, AccessShareLock);
      nblocks = RelationGetNumberOfBlocks(heapRel);
      heap_close(heapRel, AccessShareLock);

      /*
--- 292,309 ----

      /*
       * We need to know the size of the table so that we know how long to
!      * iterate on the revmap.  While we have it open, estimate the number of
!      * tuples per heap page for use later.
       */
      heapOid = IndexGetRelation(RelationGetRelid(idxRel), false);
      heapRel = heap_open(heapOid, AccessShareLock);
      nblocks = RelationGetNumberOfBlocks(heapRel);
+     if (heapRel->rd_rel->relpages != 0 && heapRel->rd_rel->reltuples > 0)
+         heap_tuples_per_page = (int)
+             ((double) heapRel->rd_rel->reltuples /
+              (BlockNumber) heapRel->rd_rel->relpages);
+     else    /* if no info, assume 100-byte tuples */
+         heap_tuples_per_page = BLCKSZ / 100;
      heap_close(heapRel, AccessShareLock);

      /*
*************** bringetbitmap(PG_FUNCTION_ARGS)
*** 447,457 ****
          ReleaseBuffer(buf);

      /*
!      * XXX We have an approximation of the number of *pages* that our scan
!      * returns, but we don't have a precise idea of the number of heap tuples
!      * involved.
       */
!     PG_RETURN_INT64(totalpages * 10);
  }

  /*
--- 455,465 ----
          ReleaseBuffer(buf);

      /*
!      * We have an approximation of the number of pages that our scan returns,
!      * but we don't have a precise idea of the number of heap tuples involved.
!      * We have to estimate based on average tuple density.
       */
!     PG_RETURN_INT64(totalpages * heap_tuples_per_page);
  }

  /*