pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Todd A. Cook <tcook@blackducksoftware.com>
To: Tomas Vondra <tomas.vondra@2ndquadrant.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
To: Thomas Munro <thomas.munro@enterprisedb.com>
Cc: Andres Freund <andres@anarazel.de>
Cc: PostgreSQL Bugs <pgsql-bugs@postgresql.org>
Subject: Re: BUG #14932: SELECT DISTINCT val FROM table gets stuck in an infinite loop
Date: Wed, 6 Dec 2017 11:58:04 -0500
Message-ID: <1773c302-2fb1-749f-8ccc-62b23617c5ac@blackducksoftware.com> (raw)
In-Reply-To: <c15378b3-d4aa-a5c0-5538-dd52904dcbc6@2ndquadrant.com>
References: <20171127185700.1470.20362@wrigleys.postgresql.org>
	<fd924fd9-8f27-d945-04a9-57b503896107@2ndquadrant.com>
	<7a22f496-92b5-d199-984e-183965918304@blackducksoftware.com>
	<20171127221041.mezzysc4zcfsqkyc@alap3.anarazel.de>
	<CAEepm=3SC9zYLKhvtZbsnwf9vEpi96WCGz1rdGLofuzBau=fqA@mail.gmail.com>
	<17357.1511841783@sss.pgh.pa.us>
	<9cd29b81-7ba1-91a2-f491-9014f4ac87e6@blackducksoftware.com>
	<2a76784c-06cb-46ec-35be-9895d144b839@2ndquadrant.com>
	<219b37b2-d740-d04a-203a-49ae422028f7@blackducksoftware.com>
	<c15378b3-d4aa-a5c0-5538-dd52904dcbc6@2ndquadrant.com>

On 12/06/17 08:33, Tomas Vondra wrote:
>
>>> Can you share the data, so that whoever fixes the bug can verify it also
>>> fixes your example?
>>
>> Sure.  It's attached.
>>
> 
> Seems the dataset has pretty much the same issue as the one reported
> before, that is
> 
> select hashint8(val), count(distinct val), count(*) from temp_f_03 group
> by 1 order by 2 desc;
> 
>        hashint8   | count | count
>      -------------+-------+-------
>       -1971396144 |    45 |    45
>        2035896403 |    42 |    42
>       -1633843397 |    30 |    30
>        1425704662 |    29 |    29
>        -455482779 |    22 |    22
>        -300163565 |    17 |    17
>       -1803963420 |    17 |    17
>        -537082846 |    14 |    14
>         603707034 |    13 |    13
>        -176369887 |    12 |    12
>        1274957136 |    11 |    11
>        1465522632 |    11 |    11
>       -1589862230 |    10 |    10
>       -1145403239 |    10 |    10
> 
> i.e. there are many hash collisions (more than in the other data set).

If hashint8() is ultimately invoked by TupleHashTableHash() in execGroups.c,
it might be magnifying the difficulties here.  The least significant bits,
which are used as the bucket number in simplehash.h, are not very well
distributed:

select val, to_hex(val), to_hex(hashint8(val)) from temp_f_03 limit 15 ;
          val          |      to_hex      |  to_hex
----------------------+------------------+----------
   4444319256653758784 | 3dad64d121468140 | 805ffffe
    554179993563924608 | 7b0d7c49a018880  | 84dffffb
  -3383965646518123872 | d109bd2c6b2982a0 | 9c3ffff7
  -4706811054739454944 | beae0c48915f8420 | 191ffff6
    618200668902031424 | 8944a3ba5d08040  | 2a3ffff0
   5074043922812601024 | 466aa01079f982c0 | 7effffee
  -8783188184262212928 | 861bd8e1b9a482c0 | a6bfffea
  -4597800992953433792 | c031545b6b128140 | b1dfffea
   8563040839807173408 | 76d608465dde8320 | 7d9fffe6
   6092569112843158816 | 548d27540c888520 | 6f9fffe6
  -7313351060369079936 | 9a81c1f558f98180 | 68ffffe5
  -1786712428165627488 | e7345283536981a0 | 73ffffe5
  -6153596242570280896 | aa9a08d20e6b8040 | ac3fffd8
     88426174078092128 | 13a272306c58360  | b57fffd8
  -5305589938458295680 | b65ec20faa4e8280 | ba9fffd3

-- todd




view thread (70+ messages)  latest in thread

Message-ID: <1773c302-2fb1-749f-8ccc-62b23617c5ac@blackducksoftware.com>
Permalink:  ../1773c302-2fb1-749f-8ccc-62b23617c5ac@blackducksoftware.com/
Also on:    postgresql.org/message-id/1773c302-2fb1-749f-8ccc-62b23617c5ac@blackducksoftware.com

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-bugs@postgresql.org
  Cc: tcook@blackducksoftware.com, tomas.vondra@2ndquadrant.com, tgl@sss.pgh.pa.us, thomas.munro@enterprisedb.com, andres@anarazel.de
  Subject: Re: BUG #14932: SELECT DISTINCT val FROM table gets stuck in an infinite loop
  In-Reply-To: <1773c302-2fb1-749f-8ccc-62b23617c5ac@blackducksoftware.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox