Message-ID: <5166C69B.1010505@ca.afilias.info>
Date: Thu, 11 Apr 2013 10:20:11 -0400
From: Steve Singer <ssinger@ca.afilias.info>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:17.0) Gecko/20130308 Thunderbird/17.0.4
MIME-Version: 1.0
To: Jeff Janes <jeff.janes@gmail.com>
CC: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: slow bitmap heap scans on pg 9.2
References: <51656E03.7000701@ca.afilias.info>
	<CAMkU=1xQXQWBw8AngvhM70K9GOGby_WAGTR3YTDZ26apv9k9pw@mail.gmail.com>
	<5165FBA1.90704@ca.afilias.info>
In-Reply-To: <5165FBA1.90704@ca.afilias.info>
Content-Type: multipart/mixed; boundary="------------000104060306020408080503"
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

This is a multi-part message in MIME format.
--------------000104060306020408080503
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

On 13-04-10 07:54 PM, Steve Singer wrote:
> On 13-04-10 02:06 PM, Jeff Janes wrote:
>> On Wed, Apr 10, 2013 at 6:49 AM, Steve Singer <ssinger@ca.afilias.info
>> <mailto:ssinger@ca.afilias.info>> wrote:
>>
>
>> I think the index recheck means your bitmap is overflowing (i.e. needing
>> more space than work_mem) and so keeping only the pages which have at
>> least one match, which means all rows in those pages need to be
>> rechecked.  How many rows does the table have?  You might be essentially
>> doing a seq scan, but with the additional overhead of the bitmap
>> machinery.  Could you do "explain (analyze,buffers)", preferably with
>> track_io_timing set to on?
>>
>
> table_b has 1,530,710,469 rows
>
> Attached is the output with track_io_timings and buffers.
>

I've done some more testing with a random_page_cost=20.

This gives me the nested-loop plan for the various date ranges I've tried.

However table_a_2 and table_b_2 are actually partition tables. This 
query only needs to look at a single partition.  When I run this same 
query against a different partition (a smaller partition, but still 
bigger than cache) it picks hash join plan involving a seq scan of 
table_b but no bitmap index scan.   On this partition the hash-join 
plans tend to take 15 minutes versus 2 minutes when I disable hashjoin 
plans. Bumping random_page_cost higher doesn't fix this.

I think the reason why it is picking the hash join based plans is 
because of

Index Scan using table_b_1_ptid_orgid_ym_unq on table_b_1 b 
(cost=0.00..503.86 rows=1 width=10) (actual time=0.016..0.017 rows=1 
loops=414249)
                     Index Cond: ((a.id = a_id) AND (organization_id = 
2) AND (year = 2013) AND (month = 3))
                     Filter: (product_id = 1)

I think we are over-estimating the cost of the index scans in the inner 
loop.  This seems similar to what was discussed a few months ago
http://www.postgresql.org/message-id/092a01cdd230$ff6143c0$fe23cb40$@foo.me.uk

This version of PG should have 3e9960e9d935e7e applied.  I am trying to 
get the database copied to a machine where I can easily switch PG 
versions and test this against something prior to that commit and also 
against a 9.3 build.

Steve


>
>
>>   Cheers,
>>
>> Jeff
>


--------------000104060306020408080503
Content-Type: text/plain; charset=UTF-8;
 name="nestedloop_1.txt"
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
 filename="nestedloop_1.txt"

cmVzZXQgZW5hYmxlX2hhc2hqb2luPWZhbHNlOwpzZXQgZW5hYmxlX21lcmdl
am9pbj1mYWxzZTsKc2V0IHJhbmRvbV9wYWdlX2Nvc3Q9MjA7CgpIYXNoQWdn
cmVnYXRlICAoY29zdD0xMjYyMzMxNTguMDEuLjEyNjIzMzE2My4yMSByb3dz
PTM0NyB3aWR0aD01MykgKGFjdHVhbCB0aW1lPTgyNDcxLjk2My4uODI0NzEu
OTgzIHJvd3M9MjYgbG9vcHM9MSkKICAtPiAgTmVzdGVkIExvb3AgIChjb3N0
PTAuMDAuLjEyNTk3NDg0Ny40OCByb3dzPTI0ODk3NCB3aWR0aD01MykgKGFj
dHVhbCB0aW1lPTQ3My41NTguLjM5ODQ2Ljc5OSByb3dzPTQxNDI0OSBsb29w
cz0xKQogICAgICAgIEpvaW4gRmlsdGVyOiAoYS5wcm9kdWN0X29wZXJhdGlv
biA9IG8uaWQpCiAgICAgICAgUm93cyBSZW1vdmVkIGJ5IEpvaW4gRmlsdGVy
OiAxNjU2OTk2MAogICAgICAgIC0+ICBOZXN0ZWQgTG9vcCBMZWZ0IEpvaW4g
IChjb3N0PTAuMDAuLjEyNTgyMTcyNi45NiByb3dzPTI0ODk3NCB3aWR0aD00
MikgKGFjdHVhbCB0aW1lPTQ3My41MDAuLjM1MDkwLjgxNiByb3dzPTQxNDI0
OSBsb29wcz0xKQogICAgICAgICAgICAgIC0+ICBJbmRleCBTY2FuIHVzaW5n
IHRyYW5zX3Bvc3RlZF8xX3RyYW5zX2RhdGVfaWR4IG9uIHRhYmxlX2FfMSBh
ICAoY29zdD0wLjAwLi4zNzE5NzMuMzUgcm93cz0yNDg5NzQgd2lkdGg9MzYp
IChhY3R1YWwgdGltZT04MS4wODAuLjI3MjQyLjgyNSByb3dzPTQxNDI0OSBs
b29wcz0xKQogICAgICAgICAgICAgICAgICAgIEluZGV4IENvbmQ6ICgodHJh
bnNhY3Rpb25fZGF0ZSA+PSAnMjAxMy0wMS0wMSAwMDowMDowMCswMCc6OnRp
bWVzdGFtcCB3aXRoIHRpbWUgem9uZSkgQU5EICh0cmFuc2FjdGlvbl9kYXRl
IDwgJzIwMTMtMDItMDEgMDA6MDA6MDArMDAnOjp0aW1lc3RhbXAgd2l0aCB0
aW1lIHpvbmUpKQogICAgICAgICAgICAgICAgICAgIEZpbHRlcjogKCh0d2lu
X2lkIElTIE5VTEwpIEFORCAocHJvZHVjdF9pZCA9IDEpIEFORCAoKHRyYW5z
X3R5cGVfaWQgPSBBTlkgKCd7MiwzLDR9Jzo6aW50ZWdlcltdKSkgT1IgKHRy
YW5zX3R5cGVfaWQgSVMgTlVMTCkpKQogICAgICAgICAgICAgICAgICAgIFJv
d3MgUmVtb3ZlZCBieSBGaWx0ZXI6IDU0NjY0MQogICAgICAgICAgICAgIC0+
ICBJbmRleCBTY2FuIHVzaW5nIHRhYmxlX2JfMV9wdGlkX29yZ2lkX3ltX3Vu
cSBvbiB0YWJsZV9iXzEgYiAgKGNvc3Q9MC4wMC4uNTAzLjg2IHJvd3M9MSB3
aWR0aD0xMCkgKGFjdHVhbCB0aW1lPTAuMDE2Li4wLjAxNyByb3dzPTEgbG9v
cHM9NDE0MjQ5KQogICAgICAgICAgICAgICAgICAgIEluZGV4IENvbmQ6ICgo
YS5pZCA9IGFfaWQpIEFORCAob3JnYW5pemF0aW9uX2lkID0gMikgQU5EICh5
ZWFyID0gMjAxMykgQU5EIChtb250aCA9IDMpKQogICAgICAgICAgICAgICAg
ICAgIEZpbHRlcjogKHByb2R1Y3RfaWQgPSAxKQogICAgICAgIC0+ICBNYXRl
cmlhbGl6ZSAgKGNvc3Q9MC4wMC4uMS42MiByb3dzPTQxIHdpZHRoPTE5KSAo
YWN0dWFsIHRpbWU9MC4wMDAuLjAuMDA0IHJvd3M9NDEgbG9vcHM9NDE0MjQ5
KQogICAgICAgICAgICAgIC0+ICBTZXEgU2NhbiBvbiBwcm9kdWN0X29wZXJh
dGlvbnMgbyAgKGNvc3Q9MC4wMC4uMS40MSByb3dzPTQxIHdpZHRoPTE5KSAo
YWN0dWFsIHRpbWU9MC4wMjYuLjAuMDM2IHJvd3M9NDEgbG9vcHM9MSkKVG90
YWwgcnVudGltZTogODI0NzIuMjk0IG1zCigxNSByb3dzKQoKc2V0IGVuYWJs
ZV9oYXNoam9pbj10cnVlOwpzZXQgZW5hYmxlX21lcmdlam9pbj10cnVlOwpz
ZXQgcmFuZG9tX3BhZ2VfY29zdD0yMDsKCkhhc2hBZ2dyZWdhdGUgIChjb3N0
PTE5OTk4MTg5LjU0Li4xOTk5ODE5NC43NSByb3dzPTM0NyB3aWR0aD01Mykg
KGFjdHVhbCB0aW1lPTgyNTc5NS40MDMuLjgyNTc5NS40MjAgcm93cz0yNiBs
b29wcz0xKQogIC0+ICBOZXN0ZWQgTG9vcCAgKGNvc3Q9Mzc1MDg1LjUyLi4x
OTczOTg3OS4wMiByb3dzPTI0ODk3NCB3aWR0aD01MykgKGFjdHVhbCB0aW1l
PTcyNzA3NS4zMDAuLjc4MjIzMi40MTcgcm93cz00MTQyNDkgbG9vcHM9MSkK
ICAgICAgICBKb2luIEZpbHRlcjogKGEucHJvZHVjdF9vcGVyYXRpb24gPSBv
LmlkKQogICAgICAgIFJvd3MgUmVtb3ZlZCBieSBKb2luIEZpbHRlcjogMTY1
Njk5NjAKICAgICAgICAtPiAgSGFzaCBSaWdodCBKb2luICAoY29zdD0zNzUw
ODUuNTIuLjE5NTg2NzU4LjUwIHJvd3M9MjQ4OTc0IHdpZHRoPTQyKSAoYWN0
dWFsIHRpbWU9NzI3MDc1LjAzNC4uNzc3NTMwLjU1OSByb3dzPTQxNDI0OSBs
b29wcz0xKQogICAgICAgICAgICAgIEhhc2ggQ29uZDogKGIuYV9pZCA9IGEu
aWQpCiAgICAgICAgICAgICAgLT4gIFNlcSBTY2FuIG9uIHRhYmxlX2JfMSBi
ICAoY29zdD0wLjAwLi4xOTE1Mzk5Ni4yMCByb3dzPTQ2MDU0ODYgd2lkdGg9
MTApIChhY3R1YWwgdGltZT0xOTM4MTMuNjQ1Li43MzM1NzQuOTAzIHJvd3M9
NjQxNjg0MCBsb29wcz0xKQogICAgICAgICAgICAgICAgICAgIEZpbHRlcjog
KChwcm9kdWN0X2lkID0gMSkgQU5EIChvcmdhbml6YXRpb25faWQgPSAyKSBB
TkQgKHllYXIgPSAyMDEzKSBBTkQgKG1vbnRoID0gMykpCiAgICAgICAgICAg
ICAgICAgICAgUm93cyBSZW1vdmVkIGJ5IEZpbHRlcjogNTk2ODY5MTMyCiAg
ICAgICAgICAgICAgLT4gIEhhc2ggIChjb3N0PTM3MTk3My4zNS4uMzcxOTcz
LjM1IHJvd3M9MjQ4OTc0IHdpZHRoPTM2KSAoYWN0dWFsIHRpbWU9MjM5OTQu
ODMyLi4yMzk5NC44MzIgcm93cz00MTQyNDkgbG9vcHM9MSkKICAgICAgICAg
ICAgICAgICAgICBCdWNrZXRzOiAzMjc2OCAgQmF0Y2hlczogMSAgTWVtb3J5
IFVzYWdlOiAyNzUwOWtCCiAgICAgICAgICAgICAgICAgICAgLT4gIEluZGV4
IFNjYW4gdXNpbmcgdGFibGVfYV8xX3RyYW5zX2RhdGVfaWR4IG9uIHRhYmxl
X2FfMSBhICAoY29zdD0wLjAwLi4zNzE5NzMuMzUgcm93cz0yNDg5NzQgd2lk
dGg9MzYpIChhY3R1YWwgdGltZT0xLjA1MS4uMjM2MTUuNzYxIHJvd3M9NDE0
MjQ5IGxvb3BzPTEpCiAgICAgICAgICAgICAgICAgICAgICAgICAgSW5kZXgg
Q29uZDogKCh0cmFuc2FjdGlvbl9kYXRlID49ICcyMDEzLTAxLTAxIDAwOjAw
OjAwKzAwJzo6dGltZXN0YW1wIHdpdGggdGltZSB6b25lKSBBTkQgKHRyYW5z
YWN0aW9uX2RhdGUgPCAnMjAxMy0wMi0wMSAwMDowMDowMCswMCc6OnRpbWVz
dGFtcCB3aXRoIHRpbWUgem9uZSkpCiAgICAgICAgICAgICAgICAgICAgICAg
ICAgRmlsdGVyOiAoKHR3aW5faWQgSVMgTlVMTCkgQU5EIChwcm9kdWN0X2lk
ID0gMSkgQU5EICgodHJhbnNfdHlwZV9pZCA9IEFOWSAoJ3syLDMsNH0nOjpp
bnRlZ2VyW10pKSBPUiAodHJhbnNfdHlwZV9pZCBJUyBOVUxMKSkpCiAgICAg
ICAgICAgICAgICAgICAgICAgICAgUm93cyBSZW1vdmVkIGJ5IEZpbHRlcjog
NTQ2NjQxCiAgICAgICAgLT4gIE1hdGVyaWFsaXplICAoY29zdD0wLjAwLi4x
LjYyIHJvd3M9NDEgd2lkdGg9MTkpIChhY3R1YWwgdGltZT0wLjAwMC4uMC4w
MDQgcm93cz00MSBsb29wcz00MTQyNDkpCiAgICAgICAgICAgICAgLT4gIFNl
cSBTY2FuIG9uIHByb2R1Y3Rfb3BlcmF0aW9ucyBvICAoY29zdD0wLjAwLi4x
LjQxIHJvd3M9NDEgd2lkdGg9MTkpIChhY3R1YWwgdGltZT0wLjE3NS4uMC4x
ODkgcm93cz00MSBsb29wcz0xKQpUb3RhbCBydW50aW1lOiA4MjU4MDIuNDk2
IG1zCg==

--------------000104060306020408080503
Content-Type: text/plain
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
MIME-Version: 1.0


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

--------------000104060306020408080503--