On 19 May 2002, Andrew McMillan wrote:
> On Sun, 2002-05-19 at 13:07, Keary Suska wrote:
> >
> > > For more complex queries, however, the plans are less likely to differ
> > >
> > Actually, as you can see with my examples above, the reverse is true, as the
> > simple query produced the same plan. The more complex the query, the more
> > choices the optimizer has for picking the best case, which unfortunately is
> > not always the truly best case.
>
> No, I meant _complex_ plans:
>
> pcno=# explain select * from vwmbradr where centre_id = 56;
> NOTICE: QUERY PLAN:
>
> Subquery Scan vwmbradr (cost=636.68..636.68 rows=1 width=418)
> -> Sort (cost=636.68..636.68 rows=1 width=418)
> -> Nested Loop (cost=0.00..636.67 rows=1 width=418)
> -> Nested Loop (cost=0.00..630.67 rows=1 width=340)
> -> Nested Loop (cost=0.00..624.68 rows=1
> width=254)
> -> Index Scan using primary_centre_id_index
> on constituents (cost=0.00..622.35 rows=1 width=212)
> -> Seq Scan on centres (cost=0.00..1.59
> rows=59 width=42)
> -> Index Scan using constituents_pkey on
> constituents (cost=0.00..5.98 rows=1 width=86)
> -> Index Scan using constituents_pkey on constituents
> (cost=0.00..5.98 rows=1 width=78)
>
> EXPLAIN
> pcno=# explain select * from vwmbradr where centre_id = 56 limit 1;
> NOTICE: QUERY PLAN:
>
> Limit (cost=636.68..636.68 rows=1 width=418)
> -> Subquery Scan vwmbradr (cost=636.68..636.68 rows=1 width=418)
> -> Sort (cost=636.68..636.68 rows=1 width=418)
> -> Nested Loop (cost=0.00..636.67 rows=1 width=418)
> -> Nested Loop (cost=0.00..630.67 rows=1
> width=340)
> -> Nested Loop (cost=0.00..624.68 rows=1
> width=254)
> -> Index Scan using
> primary_centre_id_index on constituents (cost=0.00..622.35 rows=1
> width=212)
> -> Seq Scan on centres
> (cost=0.00..1.59 rows=59 width=42)
> -> Index Scan using constituents_pkey on
> constituents (cost=0.00..5.98 rows=1 width=86)
> -> Index Scan using constituents_pkey on
> constituents (cost=0.00..5.98 rows=1 width=78)
>
> EXPLAIN
>
>
> :-)
Remove the 'where centre_id = 56' clause, please, and post output.
I'm curious. In you example I guess both select return one single row...
It's rows=1 almost everywhere - it means no cost for many operations.
.TM.
--
____/ ____/ /
/ / / Marco Colombo
___/ ___ / / Technical Manager
/ / / ESI s.r.l.
_____/ _____/ _/ Colombo@xxxxxx
