Re: Parallel bitmap heap scan

From: Dilip Kumar <dilipbalaut(at)gmail(dot)com>
To: tushar <tushar(dot)ahuja(at)enterprisedb(dot)com>
Cc: Amit Khandekar <amitdkhan(dot)pg(at)gmail(dot)com>, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com>, Andres Freund <andres(at)anarazel(dot)de>, Robert Haas <robertmhaas(at)gmail(dot)com>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Parallel bitmap heap scan
Date: 2017-01-10 11:46:00
Message-ID: CAFiTN-s9O-7tZ2zgm089Gh5UsPhL=HFBrNgnLVh=wtBKC9j-vQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On Tue, Jan 10, 2017 at 2:19 PM, tushar <tushar(dot)ahuja(at)enterprisedb(dot)com> wrote:
> We found a regression , earlier the testcase was working fine (against the
> older patches of Parallel bitmap heap scan) but now getting a server crash
> against v8 patches.
>
> Testcase - (one of the table of TPC-H )
>
> postgres=#explain analyze verbose
> SELECT SUM(l_extendedprice) FROM lineitem
> WHERE (l_shipdate >= '1995-01-01'::date)
> AND (l_shipdate <='1996-03-31'::date);

While fixing some of the review comments in v7 and v8, I had allocated
new memory for pagetable, and missed to initialize it.

Thanks for reporting, I have fixed it in v9. After fix query is
running fine for me. Please try attached patch and confirm from your
side.

postgres=# explain analyze verbose
SELECT SUM(l_extendedprice) FROM lineitem
WHERE (l_shipdate >= '1995-01-01'::date)
AND (l_shipdate <='1996-03-31'::date);

QUERY PLAN

--------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------
Finalize Aggregate (cost=798002.46..798002.47 rows=1 width=32)
(actual time=15501.245..15501.245 rows=1 loops=1)
Output: sum(l_extendedprice)
-> Gather (cost=798002.24..798002.45 rows=2 width=32) (actual
time=15494.358..15498.919 rows=3 loops=1)
Output: (PARTIAL sum(l_extendedprice))
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=797002.24..797002.25 rows=1
width=32) (actual time=15492.937..15492.937 rows=1 loops=3)
Output: PARTIAL sum(l_extendedprice)
Worker 0: actual time=15491.218..15491.219 rows=1 loops=1
Worker 1: actual time=15493.514..15493.514 rows=1 loops=1
-> Parallel Bitmap Heap Scan on public.lineitem
(cost=147461.75..791014.31 rows=2395170 width=8) (actual
time=8553.301..15061.333 rows=189294
7 loops=3)
Output: l_extendedprice
Recheck Cond: ((lineitem.l_shipdate >=
'1995-01-01'::date) AND (lineitem.l_shipdate <= '1996-03-31'::date))
Rows Removed by Index Recheck: 6451177
Heap Blocks: exact=27963 lossy=164938
Worker 0: actual time=8548.957..15054.511
rows=1887239 loops=1
Worker 1: actual time=8554.817..15050.317
rows=1902477 loops=1
-> Bitmap Index Scan on idx_lineitem_shipdate
(cost=0.00..146024.65 rows=5748409 width=0) (actual
time=8533.701..8533.701 rows=5678841
loops=1)
Index Cond: ((lineitem.l_shipdate >=
'1995-01-01'::date) AND (lineitem.l_shipdate <= '1996-03-31'::date))
Planning time: 2.742 ms
Execution time: 15509.696 ms
(21 rows)

--
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com

Attachment Content-Type Size
0001-opt-parallelcost-refactoring-v9.patch application/octet-stream 13.3 KB
0002-hash-support-alloc-free-v9.patch application/octet-stream 6.2 KB
0003-parallel-bitmap-heap-scan-v9.patch application/octet-stream 44.1 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Ashutosh Bapat 2017-01-10 12:15:22 Re: postgres_fdw bug in 9.6
Previous Message Kyotaro HORIGUCHI 2017-01-10 11:22:23 Re: Radix tree for character conversion