The order of fields around the "=" in the WHERE conditions

Поиск
Список
Период
Сортировка
От Mike Quinn
Тема The order of fields around the "=" in the WHERE conditions
Дата
Msg-id 44313030020000E5000001DA@dp_mail.co.merced.ca.us
обсуждение исходный текст
Ответы Re: The order of fields around the "=" in the WHERE conditions  (Tom Lane <tgl@sss.pgh.pa.us>)
Re: The order of fields around the "=" in the WHERE conditions  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-performance
version

------------------------------------------------------------------------
 PostgreSQL 8.1.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC)
3.3.6
(1 row)


-- The order of fields around the "=" in the WHERE conditions
-- affects the query plan. I would rather not have to worry about
-- that. It seems that it puts me back in the place of having to
-- figure what join order is best. Here are two sql statements and
-- the query plan that is generated for each. The worst of the two
-- is first and the best one is second.
-- Mike Quinn

-- the worst way --

EXPLAIN ANALYZE
SELECT
Locts.id,
Commtypes.name
FROM
Growers
,
Locts
,
Crops
,
Commtypes
WHERE
Growers.id = '0401606'
AND
-- Commtypes.number = Crops.Commtype
Crops.Commtype = Commtypes.number
AND
Locts.number = Crops.Loct
-- Crops.Loct = Locts.number
AND
Growers.number = Locts.Grower
-- Locts.Grower = Growers.number
;
                                                                 QUERY
PLAN

---------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=18934.81..647002.69 rows=1045 width=20) (actual
time=525.267..4079.051 rows=69 loops=1)
   Join Filter: ("outer".commtype = "inner".number)
   ->  Nested Loop  (cost=18923.21..631988.31 rows=1310 width=18)
(actual time=523.867..4036.005 rows=69 loops=1)
         Join Filter: ("inner".number = "outer".loct)
         ->  Seq Scan on crops  (cost=0.00..7599.46 rows=258746
width=24) (actual time=0.006..278.656 rows=258746 loops=1)
         ->  Materialize  (cost=18923.21..18924.25 rows=104 width=18)
(actual time=0.001..0.007 rows=9 loops=258746)
               ->  Nested Loop  (cost=5503.02..18923.11 rows=104
width=18) (actual time=0.061..523.703 rows=9 loops=1)
                     Join Filter: ("outer".number = "inner".grower)
                     ->  Index Scan using growers_id on growers
(cost=0.00..3.05 rows=4 width=12) (actual time=0.016..0.024 rows=1
loops=1)
                           Index Cond: ((id)::text = '0401606'::text)
                     ->  Materialize  (cost=5503.02..7451.58
rows=112456 width=30) (actual time=0.007..433.970 rows=112456 loops=1)
                           ->  Seq Scan on locts  (cost=0.00..4566.56
rows=112456 width=30) (actual time=0.003..176.771 rows=112456 loops=1)
   ->  Materialize  (cost=11.60..16.69 rows=509 width=26) (actual
time=0.001..0.287 rows=509 loops=69)
         ->  Seq Scan on commtypes  (cost=0.00..11.09 rows=509
width=26) (actual time=0.021..0.672 rows=509 loops=1)
 Total runtime: 4081.766 ms
(15 rows)

-- the best way --

EXPLAIN ANALYZE
SELECT
Locts.id,
Commtypes.name
FROM
Growers
,
Locts
,
Crops
,
Commtypes
WHERE
Growers.id = '0401606'
AND
Commtypes.number = Crops.Commtype
-- Crops.Commtype = Commtypes.number
AND
-- Locts.number = Crops.Loct
Crops.Loct = Locts.number
AND
-- Growers.number = Locts.Grower
Locts.Grower = Growers.number
;
                                                               QUERY
PLAN

----------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..11224.18 rows=1045 width=20) (actual
time=0.259..1.172 rows=69 loops=1)
   ->  Nested Loop  (cost=0.00..5717.09 rows=1310 width=18) (actual
time=0.205..0.466 rows=69 loops=1)
         ->  Nested Loop  (cost=0.00..31.90 rows=104 width=18) (actual
time=0.141..0.171 rows=9 loops=1)
               ->  Index Scan using growers_id on growers
(cost=0.00..3.05 rows=4 width=12) (actual time=0.078..0.080 rows=1
loops=1)
                     Index Cond: ((id)::text = '0401606'::text)
               ->  Index Scan using locts_grower on locts
(cost=0.00..6.15 rows=85 width=30) (actual time=0.058..0.070 rows=9
loops=1)
                     Index Cond: (locts.grower = "outer".number)
         ->  Index Scan using crops_loct on crops  (cost=0.00..54.13
rows=43 width=24) (actual time=0.012..0.022 rows=8 loops=9)
               Index Cond: (crops.loct = "outer".number)
   ->  Index Scan using commtypes_number_key on commtypes
(cost=0.00..4.19 rows=1 width=26) (actual time=0.006..0.007 rows=1
loops=69)
         Index Cond: (commtypes.number = "outer".commtype)
 Total runtime: 1.308 ms
(12 rows)



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

Предыдущее
От: Kenji Morishige
Дата:
Сообщение: Re: optimizing db for small table with tons of updates
Следующее
От: Tom Lane
Дата:
Сообщение: Re: The order of fields around the "=" in the WHERE conditions