Re: help with getting index scan

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.