Ð ÐÑÐ, 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
Quoting Guillaume Smet <[EMAIL PROTECTED]>:
> 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.
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 an
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...
regression=# create table t1(f1 int);
CREATE TABLE
regression=# insert into t1 values(5);
INSERT 154632 1
regression=# insert into t1 values(7
> 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 shoul
Guillaume Smet <[EMAIL PROTECTED]> 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_applic
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)
- *VAC
Josh Berkus 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_ob
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 bes
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:
10 matches
Mail list logo
