Re: Talking about optimizer, my long dream

2011/2/27 Віталій Тимчишин <tivv00(at)gmail(dot)com>:
>
>
> 27 лютого 2011 р. 19:59 Robert Haas <robertmhaas(at)gmail(dot)com> написав:
>>
>> 2011/2/4 Віталій Тимчишин <tivv00(at)gmail(dot)com>:
>> > Hi, all.
>> > All this optimizer vs hint thread reminded me about crazy idea that got
>> > to
>> > my head some time ago.
>> > I currently has two problems with postgresql optimizer
>> > 1) Dictionary tables. Very usual thing is something like "select * from
>> > big_table where distionary_id = (select id from dictionary where
>> > name=value)". This works awful if dictionary_id distribution is not
>> > uniform.
>>
>> Does it work better if you write it as a join?
>>
>> SELECT b.* FROM big_table b, dictionary d WHERE b.dictionary_id = d.id
>> AND d.name = 'value'
>>
>> I would like to see a concrete example of this not working well,
>> because I've been writing queries like this (with MANY tables) for
>> years and it's usually worked very well for me.
>>
> Here you are:
>  PostgreSQL 8.4.7 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.4.real
> (Ubuntu 4.4.3-4ubuntu5) 4.4.3, 64-bit
> create table a(dict int4, val int4);
> create table b(dict int4, name text);
> create index c on a(dict);
> insert into b values (1, 'small'), (2, 'large');
> insert into a values (1,1);
> insert into a select 2,generate_series(1,10000);
> analyze a;
> analyze b;
> test=# explain analyze select * from a where dict=1;
>                                              QUERY PLAN
>
> -----------------------------------------------------------------------------------------------------
>  Index Scan using c on a  (cost=0.00..8.27 rows=1 width=8) (actual
> time=0.014..0.016 rows=1 loops=1)
>    Index Cond: (dict = 1)
>  Total runtime: 0.041 ms
> (3 rows)
> test=# explain analyze select * from a where dict=2;
>                                              QUERY PLAN
>
> -----------------------------------------------------------------------------------------------------
>  Seq Scan on a  (cost=0.00..170.01 rows=10000 width=8) (actual
> time=0.014..6.876 rows=10000 loops=1)
>    Filter: (dict = 2)
>  Total runtime: 13.419 ms
> (3 rows)
> test=# explain analyze select * from a,b where a.dict=b.dict and
> b.name='small';
>                                                 QUERY PLAN
>
> -----------------------------------------------------------------------------------------------------------
>  Hash Join  (cost=1.04..233.55 rows=5000 width=18) (actual
> time=0.047..13.159 rows=1 loops=1)
>    Hash Cond: (a.dict = b.dict)
>    ->  Seq Scan on a  (cost=0.00..145.01 rows=10001 width=8) (actual
> time=0.009..6.633 rows=10001 loops=1)
>    ->  Hash  (cost=1.02..1.02 rows=1 width=10) (actual time=0.011..0.011
> rows=1 loops=1)
>          ->  Seq Scan on b  (cost=0.00..1.02 rows=1 width=10) (actual
> time=0.006..0.008 rows=1 loops=1)
>                Filter: (name = 'small'::text)
>  Total runtime: 13.197 ms
> (7 rows)
> test=# explain analyze select * from a,b where a.dict=b.dict and
> b.name='large';
>                                                 QUERY PLAN
>
> -----------------------------------------------------------------------------------------------------------
>  Hash Join  (cost=1.04..233.55 rows=5000 width=18) (actual
> time=0.074..21.476 rows=10000 loops=1)
>    Hash Cond: (a.dict = b.dict)
>    ->  Seq Scan on a  (cost=0.00..145.01 rows=10001 width=8) (actual
> time=0.012..7.085 rows=10001 loops=1)
>    ->  Hash  (cost=1.02..1.02 rows=1 width=10) (actual time=0.021..0.021
> rows=1 loops=1)
>          ->  Seq Scan on b  (cost=0.00..1.02 rows=1 width=10) (actual
> time=0.015..0.016 rows=1 loops=1)
>                Filter: (name = 'large'::text)
>  Total runtime: 28.293 ms
> (7 rows)
> It simply don't know that small=1 and large=2, so it never uses nested loop
> + iindex scan:
> test=# set enable_hashjoin=false;
> SET
> test=# explain analyze select * from a,b where a.dict=b.dict and
> b.name='small';
>                                                    QUERY PLAN
>
> ----------------------------------------------------------------------------------------------------------------
>  Nested Loop  (cost=0.00..253.28 rows=5000 width=18) (actual
> time=0.041..0.047 rows=1 loops=1)
>    ->  Seq Scan on b  (cost=0.00..1.02 rows=1 width=10) (actual
> time=0.010..0.012 rows=1 loops=1)
>          Filter: (name = 'small'::text)
>    ->  Index Scan using c on a  (cost=0.00..189.75 rows=5000 width=8)
> (actual time=0.021..0.023 rows=1 loops=1)
>          Index Cond: (a.dict = b.dict)
>  Total runtime: 0.089 ms
> (6 rows)

Oh, I see. Interesting example.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company