Re: How to optimize this query?

Hello Markus,

Wednesday, March 22, 2006, 6:58:44 PM, you wrote:

MB> Send an EXPLAIN ANALYZE of the query along with the description of the
MB> involved tables. Also hardware information (RAM, disks, CPU), what
MB> other applications are running on that box and the parameter values in
MB> postgresql.conf that you changed from the defaults would be
MB> interesting.

Sort  (cost=21413847.71..21413867.37 rows=7864 width=107)  Sort Key: dostawcy.id_dostawcy  ->  Group
(cost=1360.03..21413073.50rows=7864 width=107)        ->  Sort  (cost=1360.03..1379.69 rows=7864 width=107)
Sort Key: towar.id_towar, towar.key2, towar.nazwa, towar.min1, towar.max1, towar.ilosc_jed, towar.ilosc_nom,
dostawcy.id_dostawcy,jednostka_miary.jednostka, towar.ilosc_paczkowa, towar.key1              ->  Hash Left Join
(cost=2.21..585.81rows=7864 width=107)                    Hash Cond: ("outer".id_jednostka_miary =
"inner".id_jednostka_miary)                   ->  Hash Left Join  (cost=1.14..466.78 rows=7864 width=103)
          Hash Cond: ("outer".id_dostawcy = "inner".id_dostawcy)                          ->  Seq Scan on towar
(cost=0.00..347.68rows=7864 width=103)                                Filter: policzalne                          ->
Hash (cost=1.11..1.11 rows=11 width=4)                                ->  Seq Scan on dostawcy  (cost=0.00..1.11
rows=11width=4)                    ->  Hash  (cost=1.06..1.06 rows=6 width=12)                          ->  Seq Scan on
jednostka_miary (cost=0.00..1.06 rows=6 width=12)        SubPlan          ->  Aggregate  (cost=2722.71..2722.72 rows=1
width=14)               ->  Nested Loop  (cost=64.33..2722.28 rows=171 width=14)                      ->  Hash Join
(cost=64.33..602.79rows=368 width=12)                            Hash Cond: ("outer".id_zlecenia = "inner".id_zlecenia)
                          ->  Seq Scan on zlecenia_elementy  (cost=0.00..488.85 rows=9185 width=20)
      ->  Hash  (cost=63.98..63.98 rows=140 width=8)                                  ->  Bitmap Heap Scan on zlecenia
(cost=6.50..63.98rows=140 width=8)                                        Recheck Cond: ((id_paczka = 52) OR (id_paczka
=50) OR (id_paczka = 53))                                        ->  BitmapOr  (cost=6.50..6.50 rows=142 width=0)
                                      ->  Bitmap Index Scan on zlecenia_id_paczka  (cost=0.00..2.17 rows=47 width=0)
                                               Index Cond: (id_paczka = 52)
->  Bitmap Index Scan on zlecenia_id_paczka  (cost=0.00..2.17 rows=47 width=0)
         Index Cond: (id_paczka = 50)                                              ->  Bitmap Index Scan on
zlecenia_id_paczka (cost=0.00..2.17 rows=47 width=0)                                                    Index Cond:
(id_paczka= 53)                      ->  Index Scan using zlezenia_towar_elementy_towar on zlecenia_towar
(cost=0.00..5.75rows=1 width=18)                            Index Cond: ((zlecenia_towar.id_zlecenia_elementy =
"outer".id_zlecenia_elementy)AND (zlecenia_towar.id_towar = $0))
 
(33 rows)

-- 
Best regards,Maciej                            mailto:piechcio@isb.com.pl