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"())