Thread: query optimization Anyone know a better way to run this query

query optimization Anyone know a better way to run this query

From
"David Blood"
Date:

 

Anyone know a better way to run this query to make it faster.

It is running on a a dual 800 pIII with 1gig ram.

 

 

 

SET ENABLE_SEQSCAN TO OFF;

Explain

select

(loginFName|| ' ' || loginLName) as Name,

loginID as PFLID,

count(1) as NumberOfLeads

from

tbllogin,

tblcold

where

coldsponsorid = tbllogin.loginid

group by

loginID,

loginFName,

LoginLName

order by

name

limit

0

 

QUERY PLAN:

Limit  (cost=1453435.17..1453435.17 rows=1 width=32)

  ->  Sort  (cost=1453435.17..1453435.17 rows=242562 width=32)

       ->  Aggregate  (cost=1407484.24..1431740.44 rows=242562 width=32)

              ->  Group  (cost=1407484.24..1425676.39 rows=2425620 width=32)

                    ->  Sort  (cost=1407484.24..1407484.24 rows=2425620 width=32)

                          ->  Merge Join  (cost=0.00..1070338.18 rows=2425620 width=32)

                                ->  Index Scan using tbllogin_pkey on tbllogin  (cost=0.00..465.90 rows=2658 width=28)

                                ->  Index Scan using spons_tblcold_index on tblcold  (cost=0.00..1039518.81 rows=2425620 width=4)