From: | Dilip Kumar <dilipbalaut(at)gmail(dot)com> |
---|---|
To: | Andres Freund <andres(at)anarazel(dot)de> |
Cc: | Robert Haas <robertmhaas(at)gmail(dot)com>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Performance degradation in Bitmapscan (commit 75ae538bc3168bf44475240d4e0487ee2f3bb376) |
Date: | 2016-11-28 04:09:40 |
Message-ID: | CAFiTN-tOw83jw5W-B8+LENrPGc-LQAUmNpoHCLMdEa4=fC5UuQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Wed, Nov 23, 2016 at 2:03 PM, Andres Freund <andres(at)anarazel(dot)de> wrote:
> Here's my WIP series addressing this and related problems. With this
> we're again noticeably faster than the dynahash implementation, in both
> the case here, and the query you brought up over IM.
>
> This definitely needs some more TLC, but the general approach seems
> good. I particularly like that it apparently allows us to increase the
> default fillfactor without much downside according to my measurements.
This patch fixes the performance problem which I have reported
upthread. Time taken in Bitmap Index Scan is back to normal which was
drastically improved by 75ae538bc3168bf44475240d4e0487ee2f3bb376
commit.
--------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------
Limit (cost=1583130.59..1583130.60 rows=1 width=32) (actual
time=14041.922..14041.923 rows=1 loops=1)
-> Aggregate (cost=1583130.59..1583130.60 rows=1 width=32)
(actual time=14041.921..14041.921 rows=1 loops=1)
-> Bitmap Heap Scan on lineitem (cost=296012.30..1577117.95
rows=1202528 width=12) (actual time=1711.899..13332.892 rows=1190658
loops=1)
Recheck Cond: ((l_shipdate >= '1995-01-01'::date) AND
(l_shipdate < '1996-01-01 00:00:00'::timestamp without time zone) AND
(l_discount >= 0.07
) AND (l_discount <= 0.09) AND (l_quantity < '25'::numeric))
Rows Removed by Index Recheck: 27585320
Heap Blocks: exact=101349 lossy=580141
-> Bitmap Index Scan on idx_lineitem_shipdate
(cost=0.00..295711.67 rows=1202528 width=0) (actual
time=1689.478..1689.478 rows=1190658 loops=
1)
Index Cond: ((l_shipdate >= '1995-01-01'::date)
AND (l_shipdate < '1996-01-01 00:00:00'::timestamp without time zone)
AND (l_discount >=
0.07) AND (l_discount <= 0.09) AND (l_quantity < '25'::numeric))
Planning time: 0.292 ms
Execution time: 14041.968 ms
(10 rows)
revenue
-----------------
1784930119.2454
(1 row)
--
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Paquier | 2016-11-28 05:01:11 | Re: [PATCH] Reload SSL certificates on SIGHUP |
Previous Message | Robert Haas | 2016-11-28 03:59:02 | Re: Autovacuum breakage from a734fd5d1 |