Query optimization problem - Mailing list pgsql-hackers

<div class="moz-text-flowed" lang="x-unicode" style="font-family: -moz-fixed; font-size: 13px;"><b>i wrote to <br />  
<aclass="moz-txt-link-abbreviated" href="mailto:pgsql-bugs@postgresql.org">pgsql-bugs@postgresql.org</a><br /> they
tellme write to<br />   <a class="moz-txt-link-abbreviated"
href="mailto:pgsql-performance@postgresql.org">pgsql-performance@postgresql.org</a><br/> they tell me write here</b><br
/>  <br /><b>I don`t whant know how optimize query myself (i know it), and i think it must do planner.</b><br /><br />
Ihave a query: <br /><br />  SELECT d1.ID, d2.ID <br />  FROM DocPrimary d1 <br />    JOIN DocPrimary d2 ON
d2.BasedOn=d1.ID<br />  WHERE (d1.ID=234409763) or (d2.ID=234409763) <br /><br /> i think what QO(Query Optimizer) can
makeit faster (now it seq scan and on <br /> million records works 7 sec) <br /> This Query very fast (use indexes) and
easymake from first query<br /><br />  SELECT d1.ID, d2.ID <br />  FROM DocPrimary d1 <br />    JOIN DocPrimary d2 ON
d2.BasedOn=d1.ID<br />  WHERE (d2.BasedOn=234409763) or (d2.ID=234409763) <br /><br /> Next plans created on table
withoutmillion rows data don`t look at exec time<br /><br />  ---------------------- <br />  Slow Query <br />
 ----------------------<br />  test=# EXPLAIN (ANALYZE on, VERBOSE on, COSTS on, BUFFERS off )SELECT d1.ID, <br />
 d2.ID<br />  test-# FROM DocPrimary d1 <br />  test-#   JOIN DocPrimary d2 ON d2.BasedOn=d1.ID <br />  test-# WHERE
(d1.ID=234409763)or (d2.ID=234409763); <br />                                                          QUERY PLAN <br
/>
 ------------------------------------------------------------------------------------------------------------------------
<br/>    Hash Join  (cost=58.15..132.35 rows=2 width=8) (actual time=0.007..0.007 <br />  rows=0 loops=1) <br />     
Output:d1.id, d2.id <br />      Hash Cond: (d2.basedon = d1.id) <br />      Join Filter: ((d1.id = 234409763) OR (d2.id
=234409763)) <br />      ->   Seq Scan on public.docprimary d2  (cost=0.00..31.40 rows=2140 <br />  width=8) (actual
time=0.002..0.002rows=0 loops=1) <br />            Output: d2.id, d2.basedon <br />      ->   Hash 
(cost=31.40..31.40rows=2140 width=4) (never executed) <br />            Output: d1.id <br />            ->   Seq
Scanon public.docprimary d1  (cost=0.00..31.40 rows=2140 <br />  width=4) (never executed) <br />                 
Output:d1.id <br /><br />  ------------------ <br />  Fast Query <br />  ------------------ <br />  test=# EXPLAIN
(ANALYZEon, VERBOSE on, COSTS on, BUFFERS off )SELECT d1.ID, <br />  d2.ID <br />  test-# FROM DocPrimary d1 <br />
 test-#  JOIN DocPrimary d2 ON d2.BasedOn=d1.ID <br />  test-# WHERE (d2.BasedOn=234409763) or (d2.ID=234409763); <br
/>                                                             QUERY PLAN <br />
 ---------------------------------------------------------------------------------------------------------------------------------
<br/>    Nested Loop  (cost=8.60..58.67 rows=12 width=8) (actual time=0.026..0.026 <br />  rows=0 loops=1) <br />     
Output:d1.id, d2.id <br />      ->   Bitmap Heap Scan on public.docprimary d2  (cost=8.60..19.31 rows=12 <br />
 width=8)(actual time=0.023..0.023 rows=0 loops=1) <br />            Output: d2.id, d2.basedon <br />           
RecheckCond: ((d2.basedon = 234409763) OR (d2.id = 234409763)) <br />            ->   BitmapOr  (cost=8.60..8.60
rows=12width=0) (actual <br />  time=0.018..0.018 rows=0 loops=1) <br />                  ->   Bitmap Index Scan on
basedon_idx (cost=0.00..4.33 <br />  rows=11 width=0) (actual time=0.008..0.008 rows=0 loops=1) <br />
                      Index Cond: (d2.basedon = 234409763) <br />                  ->   Bitmap Index Scan on id_pk 
(cost=0.00..4.26rows=1 <br />  width=0) (actual time=0.003..0.003 rows=0 loops=1) <br />                        Index
Cond:(d2.id = 234409763) <br />      ->   Index Scan using id_pk on public.docprimary d1  (cost=0.00..3.27 <br />
 rows=1width=4) (never executed) <br />            Output: d1.id, d1.basedon <br />            Index Cond: (d1.id =
d2.basedon)<br /><br /><br /> -------------------------------------------- <br /> PGver: PostgreSQL 9.0b x86 <br /> OS:
Win7x64 <br /><br /> --------------------- <br /> Create table query: <br /> --------------------- <br /><br /> CREATE
TABLEdocprimary <br /> ( <br />   id integer NOT NULL, <br />   basedon integer, <br />   CONSTRAINT id_pk PRIMARY KEY
(id)<br /> ); <br /> CREATE INDEX basedon_idx <br />   ON docprimary <br />   USING btree <br />   (basedon); <br /><br
/></div><preclass="moz-signature" cols="72">-- 
 
С уважением,
Зотов Роман Владимирович
руководитель Отдела инструментария 
ЗАО "НПО Консультант"
г.Иваново, ул. Палехская, д. 10
тел./факс: (4932) 41-01-21
mailto: <a class="moz-txt-link-abbreviated" href="mailto:zotov@oe-it.ru">zotov@oe-it.ru</a></pre>