Re: help with getting index scan

On Mon, 25 Feb 2002, Tom Lane wrote:

This is getting rather long with the explains etc. should i continue to
cc: to the list?

> "Thomas T. Thai" <tom(at)minnesota(dot)com> writes:
> > sorry tom, i'm still new to PostgreSQL. what is pg_stats and how do i use
> > it in the way you've asked?
>
> select * from pg_stats where tablename = 'foo';

yellowpages=# select * from pg_stats where tablename =
'phone_cat_address';
tablename | attname | null_frac | avg_width | n_distinct |
most_common_vals |
most_common_freqs
|
histogram_bounds
| correlation
-------------------+---------+-----------+-----------+------------+------------------------------
-----------------------------------------------------+-------------------------------------------
--------------------------------------------------------------------------------+----------------
-------------------------------------------------------------------------------+-------------
phone_cat_address | cid | 0 | 4 | 1176 |
{2,10,3,12,11,16,6,56,18293,7
5} |
{0.035,0.0283333,0.021,0.0206667,0.0133333
,0.0123333,0.00933333,0.00933333,0.009,0.00833333}
| {4,43,98,177,43
2,1603,2076,11212,15979,18262,18775}
| 1
phone_cat_address | aid | 0 | 8 | -0.526973 |
{873766,1468933,3316338,39146
78,4955422,6073155,6369270,8431670,9012026,10684620} |
{0.000666667,0.000666667,0.000666667,0.000
666667,0.000666667,0.000666667,0.000666667,0.000666667,0.000666667,0.000666667}
| {10755,1321415,
2288476,3469515,4445287,5650291,7029439,8344730,9662520,11016908,100000851228}
| 0.011702
(2 rows)

yellowpages=# select * from pg_stats where tablename = 'phone_cat';
tablename | attname | null_frac | avg_width | n_distinct |
most_common_vals | most_common_freqs
|
histogram_bounds

| correlation
-----------+---------+-----------+-----------+------------+------------------+-------------------
+------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
------------------------+-------------
phone_cat | id | 0 | 4 | -1 |
|
| {2,543,1050,1594,2106,2641,3892,5566,7244,8979,10673}

| 0.861298
phone_cat | cid | 0 | 4 | -1 |
|
| {3,1189,2182,10960,12145,13215,14410,15649,16799,18058,19247}

| 0.00397399
phone_cat | name | 0 | 29 | -1 |
|
| {"ABRASIVE CUTTING",Auditors,"Boat Covers Tops &
Upholstery-Wholesale","Cash Registers & Suppli
es (Wholesale)","Communication Equipment-Manufacturers","Decoration
Supplies-Wholesale","Framing
Contractors-Buildings","Livestock Commission",Plants-Horticultural,"State
Government-General Offi
ces",Zippers-Repairing} | 0.860659
phone_cat | popular | 0 | 5 | 2 | {N,Y}
| {0.988,0.012}
|

| 0.996101
(4 rows)

>
> >> If you set enable_seqscan to off, do you get a plan you like better?
> >> If so, what is it?
>
> > with seqscan off, the query still takes about the same about of time
> > (around 8 secs). i'd like to get it down to 1 if possible.
>
> I wanted to know what the plan and cost estimates are. Also, it'd be
> good to show EXPLAIN ANALYZE results, so that we can compare reality
> to planner cost estimates ...

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

Limit (cost=9286.33..9286.33 rows=20 width=115) (actual
time=6748.11..6748.17 r
ows=20 loops=1)
-> Sort (cost=9286.33..9286.33 rows=112 width=115) (actual
time=6748.10..674
8.12 rows=21 loops=1)
-> Nested Loop (cost=44.12..9282.51 rows=112 width=115) (actual
time=6
505.91..6701.65 rows=1745 loops=1)
-> Index Scan using zipcodes_zc_idx on zipcodes z
(cost=0.00..3.
01 rows=1 width=16) (actual time=0.97..0.97 rows=1 loops=1)
-> Materialize (cost=9272.76..9272.76 rows=337 width=99)
(actual
time=6504.94..6558.83 rows=4217 loops=1)
-> Nested Loop (cost=44.12..9272.76 rows=337
width=99) (ac
tual time=555.67..6398.70 rows=4217 loops=1)
-> Hash Join (cost=44.12..7243.86 rows=337
width=16)
(actual time=555.67..5440.17 rows=4217 loops=1)
-> Seq Scan on phone_cat_address pca
(cost=0.0
0..5512.02 rows=336702 width=12) (actual time=0.00..3376.45 rows=336702
loops=1)
-> Hash (cost=44.09..44.09 rows=11
width=4) (a
ctual time=5.86..5.86 rows=0 loops=1)
-> Index Scan using
phone_cat_nameftx_idx
on phone_cat pc (cost=0.00..44.09 rows=11 width=4) (actual
time=2.93..5.85 row
s=8 loops=1)
-> Index Scan using phone_address_aid_key on
phone_ad
dress p (cost=0.00..6.01 rows=1 width=83) (actual time=0.16..0.18 rows=1
loops=
4217)
Total runtime: 6786.19 msec

EXPLAIN
yellowpages=#

----
yellowpages=# set enable_seqscan to off;
SET VARIABLE
yellowpages=# explain analyze -> Index Scan
using pho
ne_address_aid_key on phone_ad
yellowpages-# dress p (cost=0.00..6.01 rows=1 width=83) (actual
time=0.16..0.18
rows=1 loops=
yellowpages(# 4217)
yellowpages-# Total runtime: 6786.19 msec
yellowpages-#
yellowpages-# EXPLAIN
yellowpages-# yellowpages=#
yellowpages-# ;
ERROR: parser: parse error at or near "->"
yellowpages=# set enable_seqscan to off;
SET VARIABLE
yellowpages=# explain analyze SELECT *
yellowpages-# FROM
yellowpages-# (SELECT p.name,p.address,p.city,p.state,
yellowpages(#
geo_distance(point(z.longitude,z.latitude),point(p.long,p.lat))
as dist
yellowpages(# FROM phone_address AS p, phone_cat AS pc,
phone_cat_address AS
pca, zipcodes AS z
yellowpages(# WHERE z.zip_code='55404'
yellowpages(# AND (pc.nameftx ## 'salon' AND pc.cid=pca.cid AND
pca.aid=p.a
id)
yellowpages(# ) AS ss
yellowpages-# WHERE dist < 35
yellowpages-# ORDER BY dist LIMIT 20;
NOTICE: QUERY PLAN:

Limit (cost=10799.67..10799.67 rows=20 width=115) (actual
time=1564.47..1564.53
rows=20 loops=1)
-> Sort (cost=10799.67..10799.67 rows=112 width=115) (actual
time=1564.47..1
564.49 rows=21 loops=1)
-> Nested Loop (cost=0.00..10795.85 rows=112 width=115) (actual
time=1
323.26..1517.14 rows=1745 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)
-> Materialize (cost=10786.10..10786.10 rows=337 width=99)
(actu
al time=1322.28..1372.30 rows=4217 loops=1)
-> Nested Loop (cost=0.00..10786.10 rows=337
width=99) (ac
tual time=3.90..1209.10 rows=4217 loops=1)
-> Nested Loop (cost=0.00..8757.20 rows=337
width=16
) (actual time=2.93..245.72 rows=4217 loops=1)
-> Index Scan using phone_cat_nameftx_idx
on ph
one_cat pc (cost=0.00..44.09 rows=11 width=4) (actual time=2.92..5.79
rows=8 lo
ops=1)
-> Index Scan using
phone_cat_address_cid_key o
n phone_cat_address pca (cost=0.00..812.56 rows=286 width=12) (actual
time=0.60
..21.64 rows=527 loops=8)
-> Index Scan using phone_address_aid_key on
phone_ad
dress p (cost=0.00..6.01 rows=1 width=83) (actual time=0.17..0.19 rows=1
loops=
4217)
Total runtime: 1596.69 msec

EXPLAIN