On Mon, May 04, 2015 at 12:15:54AM +0300, 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. > > 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) If you look carefully you'll see that the comparison here is done as a "double precision" and so can't use the index. If you say something like: WHERE customer_id = trunc( random()*45000)::bigint it will probably work fine. Have a nice day, -- Martijn van Oosterhout <kleptog@xxxxxxxxx> http://svana.org/kleptog/ > He who writes carelessly confesses thereby at the very outset that he does > not attach much importance to his own thoughts. -- Arthur Schopenhauer
Attachment:
signature.asc
Description: Digital signature
