Thread: Re: [HACKERS] optimization join on random value

On 5/3/15 4:15 PM, Anton 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.

Moving to -general, which is the proper list for this. (BCC -hackers)

 > soe-#         WHERE customer_id = trunc( random()*45000) ;

You could create a temp table with the random value and JOIN to it:

CREATE TEMP TABLE rnd AS SELECT random()*45000;

Another option might be to use a prepared statement:

PREPARE test AS SELECT ... WHERE customer_id = $1;
EXECUTE test( random()*45000 );
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com

Yahooo !
Many thanks !

soe=# explain  EXECUTE test( random()*45000 );
                                      QUERY PLAN
-------------------------------------------------------------------------------------
  Index Scan using addresses_cust_ix on addresses (cost=0.43..16.48
rows=3 width=84)
    Index Cond: (customer_id = 13117::bigint)

On 04.05.2015 05:10, Jim Nasby wrote:
> PREPARE test AS SELECT ... WHERE customer_id = $1;
> EXECUTE test( random()*45000 );