Thread: Bad plan after vacuum analyze
Hi,
We have some performances problem on a particular query.
We reproduced the problem on a 7.4.5 and on a 7.4.7 server.
* we load the dump in a new database
* query: it's fast (< 1ms)
* VACUUM FULL ANALYZE;
* query: it's really slow (130ms) and it's another plan
* set enable_seqscan=off;
* query: it's fast (< 1ms) : it uses the best plan
I attached the EXPLAIN ANALYZE outputs, the query and the tables
description. I really can't understand why the planner chooses this plan
and especially the line :
-> Index Scan using acs_objects_object_id_p_hhkb1 on acs_objects t98
(cost=0.00..2554.07 rows=33510 width=81) (actual time=0.043..56.392
rows=33510 loops=1).
I never saw an index scan on such a number of lines. For your
information, there are 33510 lines in this table so it scans the whole
table.
The problem seems to be the left join on the acs_objects t98 table for
the parent_application_id as if I remove it or if I change it to a
subquery, it's ok. The query is automatically generated by a persistence
layer so I can't really rewrite it.
Thanks for any help
Regards
--
Guillaume
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Merge Right Join (cost=9.27..9.48 rows=1 width=545) (actual time=129.364..129.365 rows=1 loops=1)
Merge Cond: ("outer".application_id = "inner".parent_application_id)
-> Index Scan using applicati_applicati_id_p_ogstm on applications t116 (cost=0.00..5.51 rows=28 width=20) (actual
time=0.030..0.073rows=28 loops=1)
-> Sort (cost=9.27..9.27 rows=1 width=529) (actual time=129.202..129.203 rows=1 loops=1)
Sort Key: t22.parent_application_id
-> Merge Right Join (cost=8.92..9.26 rows=1 width=529) (actual time=129.100..129.103 rows=1 loops=1)
Merge Cond: ("outer".object_id = "inner".parent_application_id)
-> Index Scan using acs_objects_object_id_p_hhkb1 on acs_objects t98 (cost=0.00..2554.07 rows=33510
width=81)(actual time=0.043..56.392 rows=33510 loops=1)
-> Sort (cost=8.92..8.93 rows=1 width=452) (actual time=0.309..0.310 rows=1 loops=1)
Sort Key: t22.parent_application_id
-> Nested Loop (cost=2.44..8.91 rows=1 width=452) (actual time=0.259..0.287 rows=1 loops=1)
-> Hash Join (cost=2.44..3.68 rows=1 width=339) (actual time=0.227..0.251 rows=1 loops=1)
Hash Cond: ("outer".application_type_id = "inner".application_type_id)
-> Seq Scan on application_types t47 (cost=0.00..1.15 rows=15 width=28) (actual
time=0.009..0.025rows=15 loops=1)
-> Hash (cost=2.44..2.44 rows=1 width=315) (actual time=0.121..0.121 rows=0 loops=1)
-> Hash Join (cost=1.01..2.44 rows=1 width=315) (actual time=0.073..0.112
rows=1loops=1)
Hash Cond: ("outer".application_id = "inner".section_id)
-> Seq Scan on applications t22 (cost=0.00..1.28 rows=28 width=70)
(actualtime=0.002..0.027 rows=28 loops=1)
-> Hash (cost=1.01..1.01 rows=1 width=245) (actual time=0.026..0.026
rows=0loops=1)
-> Seq Scan on content_sections t0 (cost=0.00..1.01 rows=1
width=245)(actual time=0.020..0.022 rows=1 loops=1)
-> Index Scan using acs_objects_object_id_p_hhkb1 on acs_objects t7 (cost=0.00..5.22
rows=1width=121) (actual time=0.016..0.017 rows=1 loops=1)
Index Cond: ("outer".application_id = t7.object_id)
Total runtime: 129.754 ms
(23 lignes)
SET
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Merge Left Join (cost=17.48..18.03 rows=1 width=545) (actual time=0.653..0.656 rows=1 loops=1)
Merge Cond: ("outer".parent_application_id = "inner".application_id)
-> Merge Left Join (cost=17.48..17.81 rows=1 width=529) (actual time=0.616..0.618 rows=1 loops=1)
Merge Cond: ("outer".parent_application_id = "inner".object_id)
-> Sort (cost=17.48..17.49 rows=1 width=452) (actual time=0.587..0.588 rows=1 loops=1)
Sort Key: t22.parent_application_id
-> Nested Loop (cost=4.68..17.47 rows=1 width=452) (actual time=0.424..0.484 rows=1 loops=1)
-> Nested Loop (cost=4.68..13.37 rows=1 width=428) (actual time=0.236..0.293 rows=1 loops=1)
-> Hash Join (cost=4.68..8.14 rows=1 width=315) (actual time=0.176..0.229 rows=1 loops=1)
Hash Cond: ("outer".application_id = "inner".section_id)
-> Index Scan using applicati_parent_app_id_idx on applications t22 (cost=0.00..3.31
rows=28width=70) (actual time=0.029..0.074 rows=28 loops=1)
-> Hash (cost=4.68..4.68 rows=1 width=245) (actual time=0.045..0.045 rows=0 loops=1)
-> Index Scan using content_sections_pk on content_sections t0
(cost=0.00..4.68rows=1 width=245) (actual time=0.039..0.042 rows=1 loops=1)
-> Index Scan using acs_objects_object_id_p_hhkb1 on acs_objects t7 (cost=0.00..5.22
rows=1width=121) (actual time=0.049..0.050 rows=1 loops=1)
Index Cond: ("outer".application_id = t7.object_id)
-> Index Scan using appli_typ_appli_typ_id_p_r5e8o on application_types t47 (cost=0.00..4.09
rows=1width=28) (actual time=0.175..0.177 rows=1 loops=1)
Index Cond: ("outer".application_type_id = t47.application_type_id)
-> Index Scan using acs_objects_object_id_p_hhkb1 on acs_objects t98 (cost=0.00..2554.07 rows=33510
width=81)(actual time=0.009..0.009 rows=1 loops=1)
-> Index Scan using applicati_applicati_id_p_ogstm on applications t116 (cost=0.00..5.51 rows=28 width=20) (actual
time=0.020..0.020rows=1 loops=1)
Total runtime: 1.001 ms
(20 lignes)
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop Left Join (cost=2.44..17.36 rows=1 width=5532) (actual time=0.441..0.466 rows=1 loops=1)
Join Filter: ("outer".parent_application_id = "inner".application_id)
-> Nested Loop Left Join (cost=2.44..15.73 rows=1 width=5214) (actual time=0.378..0.402 rows=1 loops=1)
-> Nested Loop (cost=2.44..9.70 rows=1 width=4878) (actual time=0.365..0.388 rows=1 loops=1)
-> Hash Join (cost=2.44..3.68 rows=1 width=4228) (actual time=0.315..0.334 rows=1 loops=1)
Hash Cond: ("outer".application_type_id = "inner".application_type_id)
-> Seq Scan on application_types t47 (cost=0.00..1.15 rows=15 width=326) (actual
time=0.037..0.051rows=15 loops=1)
-> Hash (cost=2.44..2.44 rows=1 width=3906) (actual time=0.115..0.115 rows=0 loops=1)
-> Hash Join (cost=1.01..2.44 rows=1 width=3906) (actual time=0.065..0.104 rows=1 loops=1)
Hash Cond: ("outer".application_id = "inner".section_id)
-> Seq Scan on applications t22 (cost=0.00..1.28 rows=28 width=1370) (actual
time=0.006..0.027rows=28 loops=1)
-> Hash (cost=1.01..1.01 rows=1 width=2536) (actual time=0.028..0.028 rows=0
loops=1)
-> Seq Scan on content_sections t0 (cost=0.00..1.01 rows=1 width=2536) (actual
time=0.018..0.020rows=1 loops=1)
-> Index Scan using acs_objects_object_id_p_hhkb1 on acs_objects t7 (cost=0.00..6.01 rows=1 width=658)
(actualtime=0.037..0.039 rows=1 loops=1)
Index Cond: ("outer".application_id = t7.object_id)
-> Index Scan using acs_objects_object_id_p_hhkb1 on acs_objects t98 (cost=0.00..6.01 rows=1 width=340)
(actualtime=0.001..0.001 rows=0 loops=1)
Index Cond: ("outer".parent_application_id = t98.object_id)
-> Seq Scan on applications t116 (cost=0.00..1.28 rows=28 width=322) (actual time=0.002..0.024 rows=28 loops=1)
Total runtime: 0.775 ms
(19 lignes)
explain analyze select t0.section_id as "this.id",
t7.object_type as "this.objectType",
t7.display_name as "this.displayName",
t7.default_domain_class as "this.defaultDomainClass",
t22.title as "this.title",
t22.description as "this.description",
t22.timestamp as "this.timestamp",
t22.application_type_id as "this.resourceType.id",
t47.workspace_application_p as "this.resourceType.isWorkspace8",
t47.has_full_page_view_p as "this.resourceType.hasFullPage9",
t47.has_embedded_view_p as "this.resourceType.hasEmbedde10",
t47.singleton_p as "this.resourceType.isSingleton",
t47.title as "this.resourceType.title",
t22.parent_application_id as "this.parentResource.id",
t98.object_type as "this.parentResource.objectType",
t98.default_domain_class as "this.parentResource.defaultD15",
t116.title as "this.parentResource.title",
t22.primary_url as "this.primaryURL",
t0.pretty_name as "this.label",
t0.page_resolver_class as "this.pageResolverClass",
t0.item_resolver_class as "this.itemResolverClass",
t0.template_resolver_class as "this.templateResolverClass",
t0.xml_generator_class as "this.xmlGeneratorClass"
from content_sections t0
join acs_objects t7 on t0.section_id = t7.object_id
join applications t22 on t0.section_id = t22.application_id
join application_types t47 on t22.application_type_id = t47.application_type_id
left join acs_objects t98 on t22.parent_application_id = t98.object_id
left join applications t116 on t22.parent_application_id = t116.application_id
where 1 = 1;
Table "public.acs_objects"
Column | Type | Modifiers
----------------------+------------------------+-----------
object_id | integer | not null
object_type | character varying(100) | not null
display_name | character varying(200) | not null
default_domain_class | character varying(100) |
Indexes:
"acs_objects_object_id_p_hhkb1" primary key, btree (object_id)
Triggers:
acs_object_dnm_ctx_add_trg AFTER INSERT ON acs_objects FOR EACH ROW EXECUTE PROCEDURE acs_object_dnm_ctx_add_fn()
acs_object_dnm_ctx_del_trg BEFORE DELETE ON acs_objects FOR EACH ROW EXECUTE PROCEDURE acs_object_dnm_ctx_del_fn()
Table "public.content_sections"
Column | Type | Modifiers
-------------------------+-------------------------+-----------
section_id | integer | not null
pretty_name | character varying(300) | not null
root_folder_id | integer | not null
templates_folder_id | integer |
staff_group_id | integer | not null
viewers_group_id | integer | not null
page_resolver_class | character varying(1000) | not null
item_resolver_class | character varying(1000) | not null
template_resolver_class | character varying(1000) | not null
xml_generator_class | character varying(1000) | not null
Indexes:
"content_sections_pk" primary key, btree (section_id)
"csections_name_un" unique, btree (pretty_name)
"content_sections_rt_folder_idx" btree (root_folder_id)
"content_sections_staff_grp_idx" btree (staff_group_id)
"content_sections_tp_folder_idx" btree (templates_folder_id)
"content_sections_vwrs_grp_idx" btree (viewers_group_id)
Foreign-key constraints:
"csections_viewers_group_id_fk" FOREIGN KEY (viewers_group_id) REFERENCES groups(group_id)
"csections_staff_group_id_fk" FOREIGN KEY (staff_group_id) REFERENCES groups(group_id)
"csections_temps_folder_id_fk" FOREIGN KEY (templates_folder_id) REFERENCES cms_folders(folder_id)
"csections_root_folder_id_fk" FOREIGN KEY (root_folder_id) REFERENCES cms_folders(folder_id)
"csections_section_id_fk" FOREIGN KEY (section_id) REFERENCES applications(application_id)
Table "public.applications"
Column | Type | Modifiers
-----------------------+--------------------------+-----------
application_id | integer | not null
title | character varying(200) |
description | character varying(4000) |
timestamp | timestamp with time zone | not null
application_type_id | integer | not null
parent_application_id | integer |
cell_number | integer |
sort_key | integer |
primary_url | character varying(4000) |
package_id | integer |
Indexes:
"applicati_applicati_id_p_ogstm" primary key, btree (application_id)
"applicati_applicati_typ_id_idx" btree (application_type_id)
"applicati_package_id_idx" btree (package_id)
"applicati_parent_app_id_idx" btree (parent_application_id)
"idx_test" btree (primary_url)
Foreign-key constraints:
"application_package_id_f_cdaho" FOREIGN KEY (package_id) REFERENCES apm_packages(package_id)
"applicati_applicati_id_f_a35g2" FOREIGN KEY (application_id) REFERENCES acs_objects(object_id)
"applica_par_applica_id_f_hvxh7" FOREIGN KEY (parent_application_id) REFERENCES applications(application_id)
"applica_applica_typ_id_f_k2bi3" FOREIGN KEY (application_type_id) REFERENCES
application_types(application_type_id)
Table "public.application_types"
Column | Type | Modifiers
-------------------------+-------------------------+-----------
application_type_id | integer | not null
object_type | character varying(100) | not null
title | character varying(200) |
description | character varying(4000) |
workspace_application_p | boolean |
has_full_page_view_p | boolean |
has_embedded_view_p | boolean |
singleton_p | boolean |
provider_id | integer |
profile | character varying(20) |
package_type_id | integer |
Indexes:
"appli_typ_appli_typ_id_p_r5e8o" primary key, btree (application_type_id)
"applicat_typ_obje_type_u_pf2uk" unique, btree (object_type)
"appli_typ_package_type_id_idx" btree (package_type_id)
"appli_typ_provider_id_idx" btree (provider_id)
Foreign-key constraints:
"applicat_typ_provid_id_f_bm274" FOREIGN KEY (provider_id) REFERENCES application_types(application_type_id)
"applica_typ_pac_typ_id_f_v80ma" FOREIGN KEY (package_type_id) REFERENCES apm_package_types(package_type_id)
Guillaume,
> We reproduced the problem on a 7.4.5 and on a 7.4.7 server.
> * we load the dump in a new database
> * query: it's fast (< 1ms)
> * VACUUM FULL ANALYZE;
> * query: it's really slow (130ms) and it's another plan
> * set enable_seqscan=off;
> * query: it's fast (< 1ms) : it uses the best plan
Looking at this, the planner seems convinced that the merge join is the
easiest way to do the OUTER JOINS, but it appears to be wrong; a nested loop
is faster.
This isn't the only place I've encountered our optimizer doing this --
underestimating the cost of a merge join. This seems to be becuase the
merge_join vs. nested_loop decision seems to be being made in the planner
without taking the double-sort and index access costs into account. This
query is an excellent example:
"good" plan:
Nested Loop Left Join (cost=2.44..17.36 rows=1 width=5532) (actual
time=0.441..0.466 rows=1 loops=1)
Join Filter: ("outer".parent_application_id = "inner".application_id)
-> Nested Loop Left Join (cost=2.44..15.73 rows=1 width=5214) (actual
time=0.378..0.402 rows=1 loops=1)
See, here the planner thinks that the 2 nested loops will cost "35".
"bad" plan:
Merge Right Join (cost=9.27..9.48 rows=1 width=545) (actual
time=129.364..129.365 rows=1 loops=1)
Merge Cond: ("outer".application_id = "inner".parent_application_id)
-> Index Scan using applicati_applicati_id_p_ogstm on applications t116
(cost=0.00..5.51 rows=28 width=20) (actual time=0.030..0.073 rows=28 loops=1)
-> Sort (cost=9.27..9.27 rows=1 width=529) (actual time=129.202..129.203
rows=1 loops=1)
Sort Key: t22.parent_application_id
-> Merge Right Join (cost=8.92..9.26 rows=1 width=529) (actual
time=129.100..129.103 rows=1 loops=1)
Merge Cond: ("outer".object_id = "inner".parent_application_id)
-> Index Scan using acs_objects_object_id_p_hhkb1 on
acs_objects t98 (cost=0.00..2554.07 rows=33510 width=81) (actual
time=0.043..56.392 rows=33510 loops=1)
-> Sort (cost=8.92..8.93 rows=1 width=452) (actual
time=0.309..0.310 rows=1 loops=1)
Sort Key: t22.parent_application_id
Here the planner chooses a merge right join. This decision seems to have been
made entirely on the basis of the cost of the join itself (total of 17)
without taking the cost of the sort and index access (total of 2600+) into
account.
Tom, is this a possible error in planner logic?
--
--Josh
Josh Berkus
Aglio Database Solutions
San Francisco
Josh Berkus <josh@agliodbs.com> writes:
> -> Merge Right Join (cost=8.92..9.26 rows=1 width=529) (actual
> time=129.100..129.103 rows=1 loops=1)
> Merge Cond: ("outer".object_id = "inner".parent_application_id)
> -> Index Scan using acs_objects_object_id_p_hhkb1 on
> acs_objects t98 (cost=0.00..2554.07 rows=33510 width=81) (actual
> time=0.043..56.392 rows=33510 loops=1)
> -> Sort (cost=8.92..8.93 rows=1 width=452) (actual
> time=0.309..0.310 rows=1 loops=1)
> Sort Key: t22.parent_application_id
> Here the planner chooses a merge right join. This decision seems to have been
> made entirely on the basis of the cost of the join itself (total of 17)
> without taking the cost of the sort and index access (total of 2600+) into
> account.
> Tom, is this a possible error in planner logic?
No, it certainly hasn't forgotten to add in the costs of the inputs.
There might be a bug here, but if so it's much more subtle than that.
It looks to me like the planner believes that the one value of
t22.parent_application_id joins to something very early in the
acs_objects_object_id_p_hhkb1 sort order, and that it will therefore not
be necessary to run the indexscan to completion (or indeed very far at
all, considering that it's including such a small fraction of the total
indexscan cost).
andrew@supernews pointed out recently that this effect doesn't apply to
the outer side of an outer join; releases before 7.4.8 mistakenly think
it does. But unless my wires are totally crossed today, acs_objects is
the nullable side here and so that error isn't applicable anyway.
So, the usual questions: have these two tables been ANALYZEd lately?
If so, can we see the pg_stats rows for the object_id and
parent_application_id columns?
regards, tom lane
Tom,
> So, the usual questions: have these two tables been ANALYZEd lately?
Yes, of course.
As I wrote in my previous mail, here is how I reproduce the problem:
- we load the dump in a new database (to be sure, there is no problem on
an index or something like that)
- query: it's fast (< 1ms)
- *VACUUM FULL ANALYZE;*
- query: it's really slow (130ms) and it's another plan
- set enable_seqscan=off;
- query: it's fast (< 1ms) : it uses the best plan
I reproduced it on two different servers exactly like that (7.4.5 and
7.4.7).
I first met the problem on a production database with a VACUUM ANALYZE
run every night (and we don't have too many inserts a day on this database).
> If so, can we see the pg_stats rows for the object_id and
> parent_application_id columns?
See attached file.
If you're interested in a dump of these tables, just tell me. There
aren't any confidential information in them.
Regards
--
Guillaume
schemaname | tablename | attname | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs |
histogram_bounds | correlation
------------+-------------+-----------+-----------+-----------+------------+------------------+-------------------+-----------------------------------------------------------------------------+-------------
public | acs_objects | object_id | 0 | 4 | -1 | | |
{1032,34143,112295,120811,285004,420038,449980,453451,457684,609292,710005}| 0.488069
(1 ligne)
schemaname | tablename | attname | null_frac | avg_width | n_distinct | most_common_vals |
most_common_freqs| histogram_bounds | correlation
------------+--------------+-----------------------+-----------+-----------+------------+------------------+-------------------+------------------+-------------
public | applications | parent_application_id | 0.928571 | 4 | 1 | {1031} |
{0.0714286} | | 1
(1 ligne)
Guillaume Smet <guillaume_ml@smet.org> writes:
>> If so, can we see the pg_stats rows for the object_id and
>> parent_application_id columns?
> See attached file.
Well, those stats certainly appear to justify the planner's belief that
the indexscan needn't run very far: the one value of
parent_application_id is 1031 and this is below the smallest value of
object_id seen by analyze. You might have better luck if you increase
the statistics target for acs_objects.object_id. (It'd be interesting
to know what fraction of acs_objects actually does have object_id < 1032.)
regards, tom lane
> Well, those stats certainly appear to justify the planner's belief that
> the indexscan needn't run very far: the one value of
> parent_application_id is 1031 and this is below the smallest value of
> object_id seen by analyze.
Yes, it seems rather logical but why does it cost so much if it should
be an effective way to find the row?
> You might have better luck if you increase
> the statistics target for acs_objects.object_id.
What do you mean exactly?
> (It'd be interesting
> to know what fraction of acs_objects actually does have object_id <
1032.)
ccm_perf=# SELECT COUNT(*) FROM acs_objects WHERE object_id<1032;
count
-------
15
ccm_perf=# SELECT COUNT(*) FROM acs_objects;
count
-------
33510
--
Guillaume
Ah-ha, I can replicate the problem. This example uses tenk1 from the
regression database, which has a column unique2 containing just the
integers 0..9999.
regression=# create table t1(f1 int);
CREATE TABLE
regression=# insert into t1 values(5);
INSERT 154632 1
regression=# insert into t1 values(7);
INSERT 154633 1
regression=# analyze t1;
ANALYZE
regression=# explain analyze select * from tenk1 right join t1 on (unique2=f1);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
Merge Right Join (cost=1.03..1.37 rows=2 width=248) (actual time=0.507..0.617 rows=2 loops=1)
Merge Cond: ("outer".unique2 = "inner".f1)
-> Index Scan using tenk1_unique2 on tenk1 (cost=0.00..498.24 rows=10024 width=244) (actual time=0.126..0.242
rows=9loops=1)
-> Sort (cost=1.03..1.03 rows=2 width=4) (actual time=0.145..0.153 rows=2 loops=1)
Sort Key: t1.f1
-> Seq Scan on t1 (cost=0.00..1.02 rows=2 width=4) (actual time=0.029..0.049 rows=2 loops=1)
Total runtime: 1.497 ms
(7 rows)
The planner correctly perceives that only a small part of the unique2
index will need to be scanned, and hence thinks the merge is cheap ---
much cheaper than if the whole index had to be scanned. And it is.
Notice that only 9 rows were actually pulled from the index. Once
we got to unique2 = 8, nodeMergejoin.c could see that no more matches
to f1 were possible.
But watch this:
regression=# insert into t1 values(null);
INSERT 154634 1
regression=# explain analyze select * from tenk1 right join t1 on (unique2=f1);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Merge Right Join (cost=1.03..1.37 rows=2 width=248) (actual time=0.560..290.874 rows=3 loops=1)
Merge Cond: ("outer".unique2 = "inner".f1)
-> Index Scan using tenk1_unique2 on tenk1 (cost=0.00..498.24 rows=10024 width=244) (actual time=0.139..106.982
rows=10000loops=1)
-> Sort (cost=1.03..1.03 rows=2 width=4) (actual time=0.181..0.194 rows=3 loops=1)
Sort Key: t1.f1
-> Seq Scan on t1 (cost=0.00..1.02 rows=2 width=4) (actual time=0.032..0.067 rows=3 loops=1)
Total runtime: 291.670 ms
(7 rows)
See what happened to the actual costs of the indexscan? All of a sudden
we had to scan the whole index because there was a null in the other
input, and nulls sort high.
I wonder if it is worth fixing nodeMergejoin.c to not even try to match
nulls to the other input. We'd have to add a check to see if the join
operator is strict or not, but it nearly always will be.
The alternative would be to make the planner only believe in the
short-circuit path occuring if it thinks that the other input is
entirely non-null ... but this seems pretty fragile, since it only
takes one null to mess things up, and ANALYZE can hardly be counted
on to detect one null in a table.
In the meantime it seems like the quickest answer for Guillaume might
be to try to avoid keeping any NULLs in parent_application_id.
regards, tom lane
Josh, Tom, Thanks for your explanations. > In the meantime it seems like the quickest answer for Guillaume might > be to try to avoid keeping any NULLs in parent_application_id. I can't do that as the majority of the applications don't have any parent one. Moreover, we use a third party application and we cannot modify all its internals. Anyway, I tried to work on the statistics as you told me and here are the results: ccm_perf=# ALTER TABLE acs_objects ALTER COLUMN object_id SET STATISTICS 30; ALTER TABLE ccm_perf=# ANALYZE acs_objects; ANALYZE ccm_perf=# \i query_section.sql ... correct plan ... Total runtime: 0.555 ms So I think I will use this solution for the moment. Thanks a lot for your help. Regards -- Guillaume
Quoting Guillaume Smet <guillaume_ml@smet.org>: > Hi, > > We have some performances problem on a particular query. ... I have to say it, this was the best laid-out set of details behind a problem I've ever seen on this list; I'm going to try live up to it, the next time I have a problem of my own.
В Срд, 11/05/2005 в 22:59 +0200, Guillaume Smet пишет: > Anyway, I tried to work on the statistics as you told me and here are > the results: > ccm_perf=# ALTER TABLE acs_objects ALTER COLUMN object_id SET STATISTICS 30; > ALTER TABLE > ccm_perf=# ANALYZE acs_objects; > ANALYZE > > ccm_perf=# \i query_section.sql > ... correct plan ... > Total runtime: 0.555 ms Given Tom's analysis, how can increasing the stats target change which plan is chosen? -- Markus Bertheau <twanger@bluetwanger.de>