Re: optimization join on random value

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