optimization join on random value - Mailing list pgsql-hackers
| From | Anton |
|---|---|
| Subject | optimization join on random value |
| Date | |
| Msg-id | 5546900A.60108@gmail.com Whole thread Raw |
| Responses |
Re: optimization join on random value
|
| List | pgsql-hackers |
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 />
pgsql-hackers by date: