--- Tom Lane <tgl@sss.pgh.pa.us> skrev:

>
> Please --- I'm still curious why the estimated cost changed so much from
> 7.4 to 8.2. I can believe a marginal change in cost leading to a plan

Is this the output you need?

logistics_82=# prepare foo(int) as select id from step_result_subset where uut_Result = $1 and
step_parent = 0;
PREPARE
logistics_82=# explain analyze execute foo(180226);
QUERY PLAN
-----------------------------------------------
Index Scan using step_result_uut_result_idx on step_result_subset (cost=0.00..563.85 rows=23
width=4) (actual time=0.069..0.069 rows=0 loops=1)
Index Cond: (uut_result = $1)
Filter: (step_parent = 0)
Total runtime: 0.112 ms
(4 rows)

Best regards,
Rolf Østvik

__________________________________________________
Bruker du Yahoo!?
Lei av spam? Yahoo! Mail har den beste spambeskyttelsen
http://no.mail.yahoo.com

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

=?iso-8859-1?q?Rolf=20=D8stvik?= <rolfostvik@yahoo.no> writes:
> Index Scan using step_result_uut_result_idx on step_result_subset (cost=0.00..563.85 rows=23
> width=4) (actual time=0.069..0.069 rows=0 loops=1)
> Index Cond: (uut_result = $1)
> Filter: (step_parent = 0)
> Total runtime: 0.112 ms
> (4 rows)

Hm, that's interesting. In your original message we have the following
for 7.4's estimate of the same plan step:

-> Index Scan using step_result_uut_result_idx on step_result_subset sr (cost=0.00..74.28 rows=2 width=8) (actual time=0.149..0.379 rows=1 loops=68)
Index Cond: ("outer".id = sr.uut_result)
Filter: (step_parent = 0)

The number-of-matching-rows estimate has gone up by a factor of 10,
which undoubtedly has a lot to do with the much higher cost estimate.
Do you have any idea why that is ... is the table really the same size
in both servers? If so, could we see the pg_stats row for
step_result_subset.uut_result on both servers?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

--- Tom Lane <tgl@sss.pgh.pa.us> skrev:

> The number-of-matching-rows estimate has gone up by a factor of 10,
> which undoubtedly has a lot to do with the much higher cost estimate.
> Do you have any idea why that is ... is the table really the same size
> in both servers? If so, could we see the pg_stats row for
> step_result_subset.uut_result on both servers?

Table step_result_subset and uut_result_subset in both databases is created from same schema
definition file and filled with data from the same data source file.

==== Server 7.4.14: ====

logistics_74# select count(*) from step_result_subset;
count
----------
17179506
(1 row)

logistics_74# select count(distinct uut_result) from step_result_subset;
count
--------
176450
(1 row)

logistics_74# analyse verbose step_result_subset;
INFO: analyzing "public.step_result_subset"
INFO: "step_result_subset": 92863 pages, 3000 rows sampled, 17179655 estimated total rows
ANALYZE

logistics_74# select * from pg_stats where tablename = step_result_subset and
attname='uut_result';
schemaname | tablename | attname | null_frac | avg_width | n_distinct |
most_common_vals |
most_common_freqs |
histogram_bounds | correlation
------------+--------------------+------------+-----------+-----------+------------+----------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------+-------------
public | step_result_subset | uut_result | 0 | 4 | 57503 |
{70335,145211,17229,20091,21827,33338,34370,42426, 47274,54146} |
{0.001,0.001,0.000666667,0.000666667,0.000666667,0 .000666667,0.000666667,0.000666667,0.000666667,0.0 00666667}
| {213,30974,51300,68529,85053,100838,114971,128126, 144230,161657,176691} | 0.951364
(1 row)


==== Server 8.2.0: ====

logistics_82# select count(*) from step_result_subset;
count
----------
17179506
(1 row)

logistics_82# select count(distinct uut_result) from step_result_subset;
count
--------
176450
(1 row)

logistics_82# analyse verbose step_result_subset;
INFO: analyzing "public.step_result_subset"
INFO: "step_result_subset": scanned 3000 of 92863 pages, containing 555000 live rows and 0 dead
rows; 3000 rows in sample, 17179655 estimated total rows
ANALYZE

logistics_# select * from pg_stats where tablename = step_result_subset and attname='uut_result';
schemaname | tablename | attname | null_frac | avg_width | n_distinct |
most_common_vals |
most_common_freqs |
histogram_bounds | correlation
------------+--------------------+------------+-----------+-----------+------------+-----------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------+-------------
public | step_result_subset | uut_result | 0 | 4 | 6516 |
{35010,111592,35790,41162,56844,57444,60709,73017, 76295,106470} |
{0.00166667,0.00166667,0.00133333,0.00133333,0.001 33333,0.00133333,0.00133333,0.00133333,0.00133333, 0.00133333}
| {147,31791,54286,70928,85996,102668,117885,130947, 144766,162098,176685} | 0.954647
(1 row)

Then on server 8.2.0 i need to set statistics to ~120 on step_result_subset.uut_result to get
n_distinct to be in same range as n_distinct on 7.4.14.

Even with a statistics value of 1000, the n_distinct value does only reach ~138 000. Is it correct
that _ideally_ the n_distinct value should be the same as "select count(distinct uut_result) from
step_result_subset"?

====
Even with better statistics on step_result_subset.uut_result neither of 7.4.14 or 8.2.0 manages to
pick the best plan when i want to select bigger datasets (in my examples that would be to set an
earlier date in the where clause for "ur.start_date_time > '2006-12-11'"). I will continue to
adjust other parameters and see what i can manage myself.

Best regards
Rolf Østvik


__________________________________________________
Bruker du Yahoo!?
Lei av spam? Yahoo! Mail har den beste spambeskyttelsen
http://no.mail.yahoo.com

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

On Fri, 2007-01-05 at 19:28 +0100, Rolf Østvik wrote:
> --- Tom Lane <tgl@sss.pgh.pa.us> skrev:
>
> > The number-of-matching-rows estimate has gone up by a factor of 10,
> > which undoubtedly has a lot to do with the much higher cost estimate.
> > Do you have any idea why that is ... is the table really the same size
> > in both servers? If so, could we see the pg_stats row for
> > step_result_subset.uut_result on both servers?
>
> Table step_result_subset and uut_result_subset in both databases is created from same schema
> definition file and filled with data from the same data source file.
>
> ==== Server 7.4.14: ====
>
> logistics_74# select count(*) from step_result_subset;
> count
> ----------
> 17179506
> (1 row)
>
> logistics_74# select count(distinct uut_result) from step_result_subset;
> count
> --------
> 176450
> (1 row)
>
> logistics_74# analyse verbose step_result_subset;
> INFO: analyzing "public.step_result_subset"
> INFO: "step_result_subset": 92863 pages, 3000 rows sampled, 17179655 estimated total rows
> ANALYZE
>
> logistics_74# select * from pg_stats where tablename = step_result_subset and
> attname='uut_result';
> schemaname | tablename | attname | null_frac | avg_width | n_distinct |
> most_common_vals |
> most_common_freqs |
> histogram_bounds | correlation
> ------------+--------------------+------------+-----------+-----------+------------+----------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------+-------------
> public | step_result_subset | uut_result | 0 | 4 | 57503 |
> {70335,145211,17229,20091,21827,33338,34370,42426, 47274,54146} |
> {0.001,0.001,0.000666667,0.000666667,0.000666667,0 .000666667,0.000666667,0.000666667,0.000666667,0.0 00666667}
> | {213,30974,51300,68529,85053,100838,114971,128126, 144230,161657,176691} | 0.951364
> (1 row)
>
>
> ==== Server 8.2.0: ====
>
> logistics_82# select count(*) from step_result_subset;
> count
> ----------
> 17179506
> (1 row)
>
> logistics_82# select count(distinct uut_result) from step_result_subset;
> count
> --------
> 176450
> (1 row)
>
> logistics_82# analyse verbose step_result_subset;
> INFO: analyzing "public.step_result_subset"
> INFO: "step_result_subset": scanned 3000 of 92863 pages, containing 555000 live rows and 0 dead
> rows; 3000 rows in sample, 17179655 estimated total rows
> ANALYZE
>
> logistics_# select * from pg_stats where tablename = step_result_subset and attname='uut_result';
> schemaname | tablename | attname | null_frac | avg_width | n_distinct |
> most_common_vals |
> most_common_freqs |
> histogram_bounds | correlation
> ------------+--------------------+------------+-----------+-----------+------------+-----------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------+-------------
> public | step_result_subset | uut_result | 0 | 4 | 6516 |
> {35010,111592,35790,41162,56844,57444,60709,73017, 76295,106470} |
> {0.00166667,0.00166667,0.00133333,0.00133333,0.001 33333,0.00133333,0.00133333,0.00133333,0.00133333, 0.00133333}
> | {147,31791,54286,70928,85996,102668,117885,130947, 144766,162098,176685} | 0.954647
> (1 row)
>
> Then on server 8.2.0 i need to set statistics to ~120 on step_result_subset.uut_result to get
> n_distinct to be in same range as n_distinct on 7.4.14.
>
> Even with a statistics value of 1000, the n_distinct value does only reach ~138 000. Is it correct
> that _ideally_ the n_distinct value should be the same as "select count(distinct uut_result) from
> step_result_subset"?

That is correct, as long as the number hasn't changed between the
ANALYZE and the select.

> Even with better statistics on step_result_subset.uut_result neither of 7.4.14 or 8.2.0 manages to
> pick the best plan when i want to select bigger datasets (in my examples that would be to set an
> earlier date in the where clause for "ur.start_date_time > '2006-12-11'"). I will continue to
> adjust other parameters and see what i can manage myself.

The ndistinct figure is very sensitive.

Could you re-run ANALYZE say 10 times each on the two release levels?
That will give you a better feel for the spread of likely values.

The distribution of rows with those values also makes a difference to
the results. ANALYZE assumes that all values are randomly distributed
within the table, so if the values are clumped together for whatever
reason the ndistinct calc is less likely to take that into account.

The larger sample size gained by increasing stats target does make a
difference.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com



---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

--- Simon Riggs <simon@2ndquadrant.com> skrev:

>
> The distribution of rows with those values also makes a difference to
> the results. ANALYZE assumes that all values are randomly distributed
> within the table, so if the values are clumped together for whatever
> reason the ndistinct calc is less likely to take that into account.

This is an important factor.

As a summary, one table is defined like this:

Table "public.step_result_subset"
Column | Type | Modifiers
-------------+---------+-----------
id | integer | not null
uut_result | integer |
step_parent | integer |
Indexes:
"step_result_subset_pkey" PRIMARY KEY, btree (id)
"step_result_subset_parent_key" btree (step_parent)
"step_result_uut_result_idx" btree (uut_result)

The values in step_result_subset.uut_result is clumped together (between 10 and 1000 of same
value, and also increasing through the table).
The rows where step_result_subset.step_parent is 0 (a special case) is distributed within the
table.

Even when i set statistics on test_result_subset.uut_result to 1000 7.4.14 picks a better plan
than 8.2.0 for some returned datasets. The best results for both 7.4.14 and 8.2.0 is if i remove
the index step_result_subset_parent_key.
I will have to check if other queries which uses step_result_subset.step_parent will be "broken"
by removing the index but i think it should be ok.


I have gotten some ideas from this thread , read some more documentation, read the archives, and
tested other queries and will try to speed up some more advance queries.

Thanks everyone.

best regards
Rolf Østvik



__________________________________________________
Bruker du Yahoo!?
Lei av spam? Yahoo! Mail har den beste spambeskyttelsen
http://no.mail.yahoo.com

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq