optimization join on random value

Поиск
Список
Период
Сортировка
От Anton Bushmelev
Тема optimization join on random value
Дата
Msg-id CAK0X_okQKj8C9FO6LMOhMmhCdsaTTpB2aqa26TvWVFoA53-HhQ@mail.gmail.com
обсуждение исходный текст
Ответы Re: optimization join on random value  (Heikki Linnakangas <hlinnaka@iki.fi>)
Список pgsql-performance
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"())


В списке pgsql-performance по дате отправления:

Предыдущее
От: Robert Klemme
Дата:
Сообщение: Re: Index Scan Backward Slow
Следующее
От: Heikki Linnakangas
Дата:
Сообщение: Re: optimization join on random value