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

On Sun, 3 Mar 2002, Masaru Sugawara wrote:
> Does a setting of the sort_mem still have a default value  ?
> Could you try a series of your challenges again after rewriting the
> postgresql.conf if so ?

i changed the default values to:

  shared_buffers = 15200
  sort_mem = 32168
  vacuum_mem = 8192
  fsync = false

> Could you, in addition, execute the following queries in stead of the
> original and show us the explain output for them ?   But I'm not sure
> they work faster than the original

the two queries below are the same except for the 'set enable_seqscan
to on' right? here are the results:

>  set enable_seqscan to on;
> explain analyze   --- (1)
> SELECT *
>   FROM (SELECT p.name, p.address, p.city, p.state,
>                geo_distance(point(z.longitude, z.latitude),
>                                                 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,
>                zipcodes AS z
>          WHERE z.zip_code='55404' AND pc.cid=pca.cid AND pca.aid=p.aid
>        ) AS ss
>  WHERE ss.dist < 35
>  ORDER BY ss.dist
> LIMIT 20;

Limit  (cost=107.13..107.13 rows=1 width=109)
       (actual time=9851.64..9851.70 rows=20 loops=1)
  ->  Sort  (cost=107.13..107.13 rows=1 width=109)
            (actual time=9851.64..9851.66 rows=21 loops=1)
        ->  Nested Loop  (cost=43.77..107.12 rows=1 width=109)
            (actual time=1462.90..9803.26 rows=1745 loops=1)
              ->  Nested Loop  (cost=43.77..104.08 rows=1 width=93)
                  (actual time=1461.97..9234.44 rows=4217 loops=1)
                    ->  Merge Join  (cost=43.77..98.31 rows=1 width=12)
                        (actual time=1461.97..8623.90 rows=4217 loops=1)
                          ->  Index Scan using
                              phone_cat_address_cidaid_key
                              on phone_cat_address pca
                              (cost=0.00..52.00 rows=1000 width=8)
                              (actual time=0.00..6447.81 rows=310533
                                loops=1)
                          ->  Sort  (cost=43.77..43.77 rows=11 width=4)
                              (actualtime=3.91..22.98 rows=4214 loops=1)
                                ->  Index Scan using phone_cat_nameftx
                                    on phone_cat
                                    (cost=0.00..43.58 rows=11 width=4)
                                    (actual time=0.97..3.90 rows=8
                                     loops=1)
                    ->  Index Scan using phone_address_aid_key
                        on phone_address p (cost=0.00..5.77 rows=1
                          width=81)
                        (actual time=0.09..0.11 rows=1 loops=4217)
              ->  Index Scan using zipcodes_zc_idx on zipcodes z
                  (cost=0.00..3.01 rows=1 width=16)
                  (actual time=0.07..0.09 rows=1 loops=4217)
Total runtime: 9853.59 msec

for this next one, i assumed you wanted 'set enable_seqscan to off;'

> explain analyze   --- (2)
> SELECT *
>   FROM (SELECT p.name, p.address, p.city, p.state,
>                geo_distance(point(z.longitude, z.latitude),
>                                                 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,
>                zipcodes AS z
>          WHERE z.zip_code='55404' AND pc.cid=pca.cid AND pca.aid=p.aid
>        ) AS ss
>  WHERE ss.dist < 35
>  ORDER BY ss.dist
> LIMIT

Limit  (cost=107.13..107.13 rows=1 width=109)
       (actual time=10228.59..10228.65 rows=20 loops=1)
  ->  Sort  (cost=107.13..107.13 rows=1 width=109)
      (actual time=10228.59..10228.61 rows=21 loops=1)
        ->  Nested Loop  (cost=43.77..107.12 rows=1 width=109)
            (actual time=1466.80..10180.24 rows=1745 loops=1)
              ->  Nested Loop  (cost=43.77..104.08 rows=1 width=93)
                  (actual time=1465.86..9598.50 rows=4217 loops=1)
                    ->  Merge Join  (cost=43.77..98.31 rows=1 width=12)
                        (actual time=1465.86..9025.95 rows=4217 loops=1)
                          ->  Index Scan using
                                phone_cat_address_cidaid_key
                              on phone_cat_address pca
                              (cost=0.00..52.00 rows=1000 width=8)
                              (actual time=0.00..6862.98 rows=310533
                                loops=1)
                          ->  Sort  (cost=43.77..43.77 rows=11 width=4)
                              (actual time=3.91..13.69 rows=4214 loops=1)
                                ->  Index Scan using phone_cat_nameftx
                                    on phone_cat
                                    (cost=0.00..43.58 rows=11 width=4)
                                    (actual time=0.97..3.90 rows=8
                                      loops=1)
                    ->  Index Scan using phone_address_aid_key
                        on phone_address p
                        (cost=0.00..5.77 rows=1 width=81)
                        (actual time=0.08..0.10 rows=1 loops=4217)
              ->  Index Scan using zipcodes_zc_idx on zipcodes z
                  (cost=0.00..3.01 rows=1 width=16)
                  (actual time=0.07..0.08 rows=1 loops=4217)
Total runtime: 10230.54 msec

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