Re: Planner not using column limit specified for one column for another column equal to first

[Віталій Тимчишин <tivv00@xxxxxxxxx>]

16 квітня 2010 р. 11:25 Hannu Krosing <hannu@xxxxxxxxxxxxxxx> написав:

On Fri, 2010-04-16 at 11:02 +0300, Віталій Тимчишин wrote:
> Hello.
>
>
> I have a query that performs very poor because there is a limit on
> join column that is not applied to other columns:
>
>
> select * from company this_ left outer join company_tag this_1_ on
> this_.id=this_1_.company_id left outer join company_measures
> companymea2_ on this_.id=companymea2_.company_id left outer join
> company_descr ces3_ on this_.id=ces3_.company_id where this_1_.tag_id
> = 7 and this_.id>50000000
> and this_1_.company_id>50000000
> order by this_.id asc limit 1000;
>
>
> (plan1.txt)
> Total runtime: 7794.692 ms
>
>
> At the same time if I apply the limit (>50000000) to other columns in
> query itself it works like a charm:
>
>
> select * from company this_ left outer join company_tag this_1_ on
> this_.id=this_1_.company_id left outer join company_measures
> companymea2_ on this_.id=companymea2_.company_id left outer join
> company_descr ces3_ on this_.id=ces3_.company_id where this_1_.tag_id
> = 7 and this_.id>50000000
> and this_1_.company_id>50000000
> and companymea2_.company_id>50000000 and ces3_.company_id>50000000
> order by this_.id asc limit 1000;

The queries are not the same.

2nd variant will not return the rows where there are no matching rows
inthis_1_ , companymea2_ or ces3_.company_id

A query equivalent to first one would be:

select * from company this_
 left outer join company_tag this_1_
              on (this_.id=this_1_.company_id

              and this_1_.company_id>50000000)

 left outer join company_measures companymea2_
              on (this_.id=companymea2_.company_id

              and companymea2_.company_id>50000000)

 left outer join company_descr ces3_
              on (this_.id=ces3_.company_id

              and ces3_.company_id>50000000)

 where this_1_.tag_id = 7
  and this_.id>50000000

 order by this_.id asc
 limit 1000;

And it's still fast (see plan in another mail), while "inner join" variant of original query is still slow.

 

I'm not sure that planner considers the above form of plan rewrite, nor
that it would make much sense to do so unless there was a really small
number of rows where x_.company_id>50000000

Actually no,

select id > 50000000, count(*) from company group by 1

f,1096042

t,5725630

I don't know why the planner wishes to perform few merges of 1000 to a million of records (and the merges is the thing that takes time) instead of taking a 1000 of records from main table and then doing a nested loop. And it must read all the records that DO NOT match the criteria for secondary tables before getting to correct records if it do not filter secondary tables with index on retrieve.

set enable_mergejoin=false helps original query, but this is another problem and first solution is simpler and can be used by planner automatically, while second requires rethinking/rewrite of LIMIT estimation logic

(Plan of nested loop attached)

Limit  (cost=0.00..2369.36 rows=1000 width=1209) (actual time=0.179..41.155 rows=1000 loops=1)
  ->  Nested Loop  (cost=0.00..15727940.41 rows=6638046 width=1209) (actual time=0.174..38.312 rows=1000 loops=1)
        ->  Nested Loop  (cost=0.00..11165483.75 rows=6701224 width=1141) (actual time=0.134..26.421 rows=1000 loops=1)
              ->  Nested Loop  (cost=0.00..5763844.03 rows=6765004 width=639) (actual time=0.066..14.389 rows=1000 loops=1)
                    ->  Index Scan using pk_comp_m on company this_  (cost=0.00..1152936.77 rows=6886598 width=623) (actual time=0.038..1.908 rows=1054 loops=1)
                          Index Cond: (id > 50000000)
                    ->  Index Scan using company_tag_company_id_idx on company_tag this_1_  (cost=0.00..0.66 rows=1 width=16) (actual time=0.004..0.006 rows=1 loops=1054)
                          Index Cond: ((this_1_.company_id)::bigint = this_.id)
                          Filter: ((this_1_.tag_id)::bigint = 7)
              ->  Index Scan using cws_company_descr_unique on company_descr ces3_  (cost=0.00..0.79 rows=1 width=502) (actual time=0.004..0.006 rows=1 loops=1000)
                    Index Cond: ((ces3_.company_id)::bigint = this_.id)
        ->  Index Scan using pk_comp_ms on company_measures companymea2_  (cost=0.00..0.67 rows=1 width=68) (actual time=0.004..0.006 rows=1 loops=1000)
              Index Cond: ((companymea2_.company_id)::bigint = this_.id)
Total runtime: 42.940 ms

-- 
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance