Thread
-
optimization join on random value
Anton <djeday84@gmail.com> — 2015-05-03T21:15:54Z
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) soe=# \d addresses; soe=# \d addresses; Table "public.addresses" Column | Type | Modifiers ------------------+-----------------------------+----------- address_id | bigint | not null customer_id | bigint | not null date_created | timestamp without time zone | not null house_no_or_name | character varying(60) | street_name | character varying(60) | town | character varying(60) | county | character varying(60) | country | character varying(60) | post_code | character varying(12) | zip_code | character varying(12) | Indexes: "addresses_pkey" PRIMARY KEY, btree (address_id) "addresses_cust_ix" btree (customer_id) Foreign-key constraints: "add_cust_fk" FOREIGN KEY (customer_id) REFERENCES customers(customer_id) DEFERRABLE same query in oracle same query use index access path: 00:05:23 (1)c##bushmelev_aa@orcl> explain plan for SELECT ADDRESS_ID, CUSTOMER_ID, DATE_CREATED, HOUSE_NO_OR_NAME, STREET_NAME, TOWN, COUNTY, COUNTRY, POST_CODE, ZIP_CODE FROM soe.ADDRESSES * WHERE customer_id = dbms_random.value ();* Explained. Elapsed: 00:00:00.05 00:05:29 (1)c##bushmelev_aa@orcl> @utlxpls PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------ Plan hash value: 317664678 ----------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2 | 150 | 5 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| ADDRESSES | 2 | 150 | 5 (0)| 00:00:01 | |* 2 | *INDEX RANGE SCAN * | ADDRESS_CUST_IX | 2 | | 3 (0)| 00:00:01 | ----------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("CUSTOMER_ID"="DBMS_RANDOM"."VALUE"()) -
Re: [HACKERS] optimization join on random value
Jim Nasby <jim.nasby@bluetreble.com> — 2015-05-04T02:10:02Z
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
-
Re: [HACKERS] optimization join on random value
Martijn van Oosterhout <kleptog@svana.org> — 2015-05-04T07:01:43Z
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@svana.org> 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
-
Re: [HACKERS] optimization join on random value
Anton <djeday84@gmail.com> — 2015-05-04T08:48:02Z
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 );