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

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


> > set enable_seqscan to on;
> > explain analyze   --- (2')
> > 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'
> >                 ORDER BY cid) 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=20934.34..20934.34 rows=20 width=93)
       (actual time=12176.87..12177.84 rows=20 loops=1)
  InitPlan
    ->  Index Scan using zipcodes_zc_idx on zipcodes z
        (cost=0.00..3.01 rows=1 width=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.00 rows=1 loops=1)
  ->  Sort  (cost=20934.34..20934.34 rows=959 width=93)
            (actual time=12176.87..12177.80 rows=21 loops=1)
        ->  Hash Join  (cost=8455.23..20886.82 rows=959 width=93)
            (actual time=5344.78..12127.58 rows=1745 loops=1)
              ->  Seq Scan on phone_address p
                  (cost=0.00..11207.20 rows=69280 width=81)
                  (actual time=0.01..5821.06 rows=105214 loops=1)
              ->  Hash  (cost=8448.04..8448.04 rows=2878 width=12)
                  (actual time=5336.00..5336.00 rows=0 loops=1)
                    ->  Hash Join  (cost=43.79..8448.04 rows=2878
width=12)
                       (actual time=388.78..5305.09 rows=4217 loops=1)
                          ->  Seq Scan on phone_cat_address pca
                              (cost=0.00..5843.01 rows=336701 width=8)
                              (actual time=0.00..3475.08 rows=336701
loops=1)
                          ->  Hash  (cost=43.77..43.77 rows=11 width=74)
                              (actual time=4.92..4.92 rows=0 loops=1)
                                ->  Subquery Scan pc
                                (cost=43.77..43.77 rows=11 width=74)
                                (actual time=4.88..4.91 rows=8 loops=1)
                                      ->  Sort  (cost=43.77..43.77
                                                 rows=11 width=74)
                                          (actual time=4.88..4.89
                                           rows=8 loops=1)
                                            ->  Index Scan using
                                     phone_cat_name_fts_idx on phone_cat
                                     (cost=0.00..43.58 rows=11 width=74)
                                     (actual time=0.97..3.90 rows=8
loops=1)
Total runtime: 12178.84 msec

--
Thomas T. Thai
Minnesota.com, Inc.