Re: The order of fields around the "=" in the WHERE - Mailing list pgsql-performance

version

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

-- After commutator added to operators of user defined type,
-- the order of fields around the "=" in WHERE conditions
-- no longer affect the query plan.

-- previously 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=0.00..17467.00 rows=954 width=20) (actual
time=0.253..1.155 rows=69 loops=1)
   ->  Nested Loop  (cost=0.00..12413.93 rows=1202 width=18) (actual
time=0.191..0.472 rows=69 loops=1)
         ->  Nested Loop  (cost=0.00..32.51 rows=104 width=18) (actual
time=0.142..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.065..0.067 rows=1
loops=1)
                     Index Cond: ((id)::text = '0401606'::text)
               ->  Index Scan using locts_grower on locts
(cost=0.00..6.23 rows=91 width=30) (actual time=0.070..0.085 rows=9
loops=1)
                     Index Cond: ("outer".number = locts.grower)
         ->  Index Scan using crops_loct on crops  (cost=0.00..118.53
rows=42 width=24) (actual time=0.011..0.021 rows=8 loops=9)
               Index Cond: ("outer".number = crops.loct)
   ->  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: ("outer".commtype = commtypes.number)
 Total runtime: 1.299 ms
(12 rows)

-- previously 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..17467.00 rows=954 width=20) (actual
time=0.063..0.947 rows=69 loops=1)
   ->  Nested Loop  (cost=0.00..12413.93 rows=1202 width=18) (actual
time=0.050..0.318 rows=69 loops=1)
         ->  Nested Loop  (cost=0.00..32.51 rows=104 width=18) (actual
time=0.036..0.064 rows=9 loops=1)
               ->  Index Scan using growers_id on growers
(cost=0.00..3.05 rows=4 width=12) (actual time=0.018..0.020 rows=1
loops=1)
                     Index Cond: ((id)::text = '0401606'::text)
               ->  Index Scan using locts_grower on locts
(cost=0.00..6.23 rows=91 width=30) (actual time=0.012..0.023 rows=9
loops=1)
                     Index Cond: (locts.grower = "outer".number)
         ->  Index Scan using crops_loct on crops  (cost=0.00..118.53
rows=42 width=24) (actual time=0.007..0.018 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.005..0.006 rows=1
loops=69)
         Index Cond: (commtypes.number = "outer".commtype)
 Total runtime: 1.091 ms
(12 rows)



>>> "Mike Quinn" <mquinn@co.merced.ca.us> 4/4/06 10:18:30 AM >>>
The datatype of the join columns is a user defined type and there are
no
commutators defined. I will fix that and retest. Thanks for the
insight.

Mike Quinn

---------------------------(end of
broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq