Re: using CURSOR with PHP

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@ESI.it