Re: Dealing with complex queries - Mailing list pgsql-general

From Tom Lane
Subject Re: Dealing with complex queries
Date
Msg-id 23267.1044586689@sss.pgh.pa.us
Whole thread Raw
In response to Re: Dealing with complex queries  (Francisco J Reyes <fran@natserv.net>)
List pgsql-general
Francisco J Reyes <fran@natserv.net> writes:
>                      ->  Nested Loop  (cost=0.00..21773591.67 rows=456 width=51) (actual time=463.34..737215.23
rows=1591loops=1) 
>                            Join Filter: ("outer".ppl_key = "inner".ppl_key)
>                            ->  Index Scan using people_pplkey on people pe  (cost=0.00..2991.02 rows=2234 width=8)
(actualtime=0.19..397.73 rows=1591 loops=1) 
>                                  Filter: (("type" = 'j'::bpchar) AND (jt_id = 0))
>                            ->  Seq Scan on pplkeys ppl  (cost=0.00..8929.70 rows=65324 width=43) (actual
time=0.06..421.59rows=6770 loops=1591) 
>                                  Filter: ("type" = 'j'::bpchar)

Seems the main problem is this innermost join --- it's using the
stupidest possible form of join.  At the very least you'd like it
to use an index on pplkeys.  Are people.ppl_key and pplkeys.ppl_key
the same datatype?  Does the latter have an index?

            regards, tom lane

pgsql-general by date:

Previous
From: Tom Lane
Date:
Subject: Re: Problem with files size in /var/lib/postgres/data/global on Debian Woody
Next
From: Tom Lane
Date:
Subject: Re: AllocSetAlloc() error message