Re: help with getting index scan - Mailing list pgsql-general

On Wed, 6 Mar 2002 00:51:15 -0600 (CST)
"Thomas T. Thai" <tom@minnesota.com> wrote:


> On Wed, 6 Mar 2002, Thomas T. Thai wrote:
>
> > ERROR:  parser: parse error at or near "WHERE"
>
> ok found it. comma before where. see explain below.
>
> > > set enable_seqscan to on;
> > > explain analyze   --- (1')
> > > SELECT *
> > >   FROM (SELECT p.name, p.address, p.city, p.state,
> > >                geo_distance((SELECT point(z.longitude, z.latitude)
> > >                                FROM zipcodes AS z
> > >                               WHERE z.zip_code='55404'),
> > >                              point(p.long, p.lat)) as dist
> > >           FROM phone_address AS p,
> > >                (SELECT * FROM phone_cat WHERE nameftx ## 'salon') AS pc,
> > >                phone_cat_address AS pca,
> > >          WHERE pc.cid = pca.cid AND pca.aid = p.aid
> > >        ) AS ss
> > >  WHERE ss.dist < 35
> > >  ORDER BY ss.dist
> > > LIMIT 20;
>
> Limit  (cost=9537.20..9537.20 rows=20 width=93)
> (actual time=5849.67..5849.73 rows=20 loops=1)
>   InitPlan
>     ->  Index Scan using zipcodes_zc_idx on zipcodes z
>     (cost=0.00..3.01 rows=1width=16)
>     (actual time=0.00..0.00 rows=1 loops=1)
>     ->  Index Scan using zipcodes_zc_idx on zipcodes z
>     (cost=0.00..3.01 rows=1 width=16)
>     (actual time=0.00..0.93 rows=1 loops=1)
>   ->  Sort  (cost=9537.20..9537.20 rows=112 width=93)
>   (actual time=5849.66..5849.68 rows=21 loops=1)
>         ->  Nested Loop  (cost=43.61..9533.38 rows=112 width=93)
>         (actual time=383.80..5798.66 rows=1745 loops=1)
>               ->  Hash Join  (cost=43.61..7574.33 rows=337 width=12)
>               (actual time=382.86..5214.98 rows=4217 loops=1)
>                     ->  Seq Scan on phone_cat_address pca
>                     (cost=0.00..5843.01 rows=336701 width=8)
>                     (actual time=0.00..3441.76 rows=336701 loops=1)
>                     ->  Hash  (cost=43.58..43.58 rows=11 width=4)
>                     (actual time=3.91..3.91 rows=0 loops=1)
>                           ->  Index Scan using phone_cat_name_fts_idx
>                           on phone_cat  (cost=0.00..43.58 rows=11 width=4)
>                           (actual time=0.00..3.90 rows=8 loops=1)
>               ->  Index Scan using phone_address_aid_key
>               on phone_address p  (cost=0.00..5.81 rows=1 width=81)
>               (actual time=0.10..0.11 rows=0 loops=4217)
> Total runtime: 5851.62 msec


 That's not bat, I guess. The query below is a try to manage to get
 a pattern like this:

   -> Nested Loop
      -> Index Scan using phone_cat_nameftx_idx on phone_cat pc
      -> Index Scan using phone_cat_address_cid_key on phone_cat_address pca

 But I have no idea any more.



set enable_seqscan to on;
explain analyze   --- (1'')
SELECT *
  FROM (SELECT p.name, p.address, p.city, p.state,
               geo_distance((SELECT point(z.longitude, z.latitude)
                               FROM zipcodes AS z
                              WHERE z.zip_code='55404'),
                             point(p.long, p.lat)) as dist
          FROM phone_address AS p,
               (SELECT pca.aid
                 FROM (SELECT * FROM phone_cat
                          WHERE nameftx ## 'salon') AS pc,
                      phone_cat_address AS pca
                 WHERE pc.cid = pca.cid
                 ORDER BY 1
               ) AS pc_pca
         WHERE pc_pca.aid = p.aid
       ) AS ss
 WHERE ss.dist < 35
 ORDER BY ss.dist
LIMIT 20;



Regards,
Masaru Sugawara