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