Thread: optimization join on random value

Hello guru of postgres,  it's possoble to tune query with join on random string ? <br /> i know that it is not real
lifeexample, but i need it for tests.<br /><br /><tt>soe=# explain </tt><tt><br /></tt><tt>soe-#  SELECT  
ADDRESS_ID,      </tt><tt><br /></tt><tt>soe-#           CUSTOMER_ID,       </tt><tt><br /></tt><tt>soe-#          
DATE_CREATED,      </tt><tt><br /></tt><tt>soe-#           HOUSE_NO_OR_NAME,       </tt><tt><br
/></tt><tt>soe-#          STREET_NAME,       </tt><tt><br /></tt><tt>soe-#           TOWN,       </tt><tt><br
/></tt><tt>soe-#          COUNTY,       </tt><tt><br /></tt><tt>soe-#           COUNTRY,       </tt><tt><br
/></tt><tt>soe-#          POST_CODE,       </tt><tt><br /></tt><tt>soe-#           ZIP_CODE       </tt><tt><br
/></tt><tt>soe-#        FROM ADDRESSES      </tt><tt><br /></tt><tt>soe-#         WHERE customer_id = trunc(
random()*45000);</tt><tt><br /></tt><tt>                                        QUERY
PLAN                                        </tt><tt><br
/></tt><tt>-------------------------------------------------------------------------------------------</tt><tt><br
/></tt><tt> SeqScan on addresses  (cost=0.00..165714.00 rows=22500 width=84)</tt><tt><br /></tt><tt>   Filter:
((customer_id)::doubleprecision = trunc((random() * 45000::double precision)))</tt><tt><br /></tt><tt>(2
rows)</tt><tt><br/></tt><tt><br /></tt><tt>soe=# \d addresses;</tt><tt><br /></tt><tt>soe=# \d addresses;</tt><tt><br
/></tt><tt>                 Table
"public.addresses"                                                                                      </tt><tt><br
/></tt><tt>     Column      |            Type             |
Modifiers                                                                     </tt><tt><br
/></tt><tt>------------------+-----------------------------+-----------                                                                   
 </tt><tt><br/></tt><tt> address_id       | bigint                      | not
null                                                                      </tt><tt><br /></tt><tt> customer_id      |
bigint                     | not null                                                                     
 </tt><tt><br/></tt><tt> date_created     | timestamp without time zone | not
null                                                                      </tt><tt><br /></tt><tt> house_no_or_name |
charactervarying(60)       |                                                                              
 </tt><tt><br/></tt><tt> street_name      | character varying(60)      
|                                                                               </tt><tt><br
/></tt><tt> town            | character varying(60)      
|                                                                               </tt><tt><br
/></tt><tt> county          | character varying(60)      
|                                                                               </tt><tt><br
/></tt><tt> country         | character varying(60)      
|                                                                               </tt><tt><br
/></tt><tt> post_code       | character varying(12)      
|                                                                               </tt><tt><br
/></tt><tt> zip_code        | character varying(12)      
|                                                                               </tt><tt><br
/></tt><tt>Indexes:                                                                                                                       
 </tt><tt><br/></tt><tt>    "addresses_pkey" PRIMARY KEY, btree
(address_id)                                                                            </tt><tt><br /></tt><tt>   
"addresses_cust_ix"btree
(customer_id)                                                                                     </tt><tt><br
/></tt><tt>Foreign-key
constraints:                                                                                                       
 </tt><tt><br/></tt><tt>    "add_cust_fk" FOREIGN KEY (customer_id) REFERENCES customers(customer_id) DEFERRABLE    
</tt><tt><br/></tt><br /><br /><br /> same query in oracle same query use index access path:<br /><br /><tt>00:05:23
(1)c##bushmelev_aa@orcl>explain plan for</tt><br /><tt> SELECT   ADDRESS_ID,       </tt><tt><br /></tt><tt>         
CUSTOMER_ID,      </tt><tt><br /></tt><tt>          DATE_CREATED,       </tt><tt><br /></tt><tt>         
HOUSE_NO_OR_NAME,      </tt><tt><br /></tt><tt>          STREET_NAME,       </tt><tt><br /></tt><tt>         
TOWN,      </tt><tt><br /></tt><tt>          COUNTY,       </tt><tt><br /></tt><tt>          COUNTRY,      
</tt><tt><br/></tt><tt>          POST_CODE,       </tt><tt><br /></tt><tt>          ZIP_CODE       </tt><tt><br
/></tt><tt>       FROM soe.ADDRESSES      </tt><tt><br /></tt><b><tt> WHERE customer_id = <font
color="#ff0000">dbms_random.value();</font></tt></b><tt><br /></tt><tt><br /></tt><tt>Explained.</tt><tt><br
/></tt><tt><br/></tt><tt>Elapsed: 00:00:00.05</tt><tt><br /></tt><tt>00:05:29 (1)c##bushmelev_aa@orcl>
@utlxpls</tt><tt><br/></tt><tt><br /></tt><tt>PLAN_TABLE_OUTPUT</tt><tt><br
/></tt><tt>------------------------------------------------------------------------------</tt><tt><br/></tt><tt>Plan
hashvalue: 317664678</tt><tt><br /></tt><tt><br
/></tt><tt>-----------------------------------------------------------------------------------------------</tt><tt><br
/></tt><tt>|Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)| Time     |</tt><tt><br
/></tt><tt>-----------------------------------------------------------------------------------------------</tt><tt><br
/></tt><tt>|  0 | SELECT STATEMENT            |                 |     2 |   150 |     5   (0)| 00:00:01 |</tt><tt><br
/></tt><tt>|  1 |  TABLE ACCESS BY INDEX ROWID| ADDRESSES       |     2 |   150 |     5   (0)| 00:00:01 |</tt><tt><br
/></tt><tt>|* 2 |   <b>INDEX RANGE SCAN </b>         | ADDRESS_CUST_IX |     2 |       |     3   (0)| 00:00:01
|</tt><tt><br
/></tt><tt>-----------------------------------------------------------------------------------------------<br/><br
/></tt><tt>PredicateInformation (identified by operation id): <br
/></tt><tt>---------------------------------------------------</tt><tt><br/></tt><tt>   2 -
access("CUSTOMER_ID"="DBMS_RANDOM"."VALUE"())</tt><tt><br/></tt><br /> 

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