Message-ID: <5546930E.5080201@iki.fi>
Date: Mon, 04 May 2015 00:28:46 +0300
From: Heikki Linnakangas <hlinnaka@iki.fi>
Reply-To: hlinnaka@iki.fi
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:31.0) Gecko/20100101 Icedove/31.6.0
MIME-Version: 1.0
To: Anton Bushmelev <djeday84@gmail.com>,
 pgsql-performance@postgresql.org
Subject: Re: optimization join on random value
References: 
 <CAK0X_okQKj8C9FO6LMOhMmhCdsaTTpB2aqa26TvWVFoA53-HhQ@mail.gmail.com>
In-Reply-To: 
 <CAK0X_okQKj8C9FO6LMOhMmhCdsaTTpB2aqa26TvWVFoA53-HhQ@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

On 05/04/2015 12:23 AM, Anton Bushmelev wrote:
>   Hello guru of postgres,  it's possoble to tune query with join on random
> string ?
> i know that it is not real life example, but i need it for tests.
>
> soe=# explain
> soe-#  SELECT   ADDRESS_ID,
> soe-#           CUSTOMER_ID,
> soe-#           DATE_CREATED,
> soe-#           HOUSE_NO_OR_NAME,
> soe-#           STREET_NAME,
> soe-#           TOWN,
> soe-#           COUNTY,
> soe-#           COUNTRY,
> soe-#           POST_CODE,
> soe-#           ZIP_CODE
> soe-#         FROM ADDRESSES
> soe-#         WHERE customer_id = trunc( random()*45000) ;
>                                          QUERY
> PLAN
> -------------------------------------------------------------------------------------------
>   Seq Scan on addresses  (cost=0.00..165714.00 rows=22500 width=84)
>     Filter: ((customer_id)::double precision = trunc((random() *
> 45000::double precision)))
> (2 rows)
>

There are two problems here that prohibit the index from being used:

1. random() is volatile, so it's recalculated for each row.
2. For the comparison, customer_id is cast to a float, and the index is 
on the bigint value.

To work around the first problem, put the random() call inside a 
subquery. And for the second problem, cast to bigint.

SELECT ... FROM addresses
WHERE customer_id = (SELECT random()*45000)::bigint

- Heikki


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