Thread: Analyze makes queries slow...
Hi all, I posted this problem on the sql list, and was referred to this list in stead. I have attached an sql statement that normally runs under 3 minutes. That is, until I vacuum analyze the database (or just the tables in the query), then the same query runs longer than 12 hours, and I have to kill it. However 90% of queries are faster after analyzing on this database, there are two or three, including this one that takes for ever. I have tried to reverse engineer the explain plan from before analyzing, to come up with an sql statement, using proper joins, to force the planner to do the original join, but although I came close, I never got the same result as the original query. I suspect that this might be caused by some of the crazy indexes that were built on some of these tables, but I can't really do much about that, unless I can come up with a very good reason to nuke them. I also attached the "create table" statements for all the tables, as well as a row count of each. Can somebody help me with guidelines or something similar, to understand exactly what is happening in the explain plan. TIA Stefan
Attachment
Stef <svb@ucs.co.za> writes:
> I have attached an sql statement that normally runs under 3 minutes.
> That is, until I vacuum analyze the database (or just the tables in the query),
> then the same query runs longer than 12 hours, and I have to kill it.
Could we see the results of "EXPLAIN ANALYZE", rather than just EXPLAIN,
for the un-analyzed case? I won't make you do it for the analyzed case ;-)
but when dealing with a plan-selection problem the planner's estimates
are obviously not to be trusted.
Also, what do you see in pg_stats (after analyzing) for each of the
tables used in the query?
And what PG version is this, exactly?
regards, tom lane
PS: in case you don't know this already, an easy way to get back to the
un-analyzed state is "DELETE FROM pg_statistics".
Hi Tom, Thanks for responding. I got as much info as I could : On Mon, 11 Aug 2003 11:43:45 -0400 Tom Lane <tgl@sss.pgh.pa.us> wrote: => Could we see the results of "EXPLAIN ANALYZE", rather than just EXPLAIN, => for the un-analyzed case? Attached the output of this. => Also, what do you see in pg_stats (after analyzing) for each of the => tables used in the query? I attached a file in csv format of pg_stats after analyzing. (With the columns selected on the top line) It looks like cached values for (quite a lot of?) the table columns. I would assume it stores the most commonly selected values for every column with an index. Don't know if I'm correct. => And what PG version is this, exactly? PostgreSQL 7.3.1 Kind regards Stefan
Attachment
Stef <svb@ucs.co.za> writes:
> => Could we see the results of "EXPLAIN ANALYZE", rather than just EXPLAIN,
> => for the un-analyzed case?
> Attached the output of this.
Hmm... not immediately obvious where it's going wrong. Could you try
this (after ANALYZE):
set enable_mergejoin to off;
explain analyze ... query ...
If it finishes in a reasonable amount of time, send the explain output.
regards, tom lane
Stef <svb@ucs.co.za> writes:
> => And what PG version is this, exactly?
> PostgreSQL 7.3.1
Ah, I think I see it: you are getting burnt by a mergejoin estimation
bug that was fixed in 7.3.2. Please update (you might as well go to
7.3.4 while you're at it) and see if the results improve.
regards, tom lane
On Mon, Aug 11, 2003 at 03:58:41PM +0200, Stef wrote:
> I have attached an sql statement that normally runs under 3 minutes.
> That is, until I vacuum analyze the database (or just the tables in the query),
> then the same query runs longer than 12 hours, and I have to kill it.
Hmm, I have noticed similar problem with a query with order by ... limit clause.Although it runs only 10 times slower
afteranalyze :)
The query joins one big table (20 000 rows) with several small tables
(200-4000 rows) than order by "primary key of big table" limit 20
Without this order by ... limit clause the query is 5 times faster after
analyze.
Looking into explain analyze outputs:
1. Before vacuum analyze a planer chooses nested loop, the deepest is:
-> Nested Loop (cost=0.00..116866.54 rows=19286 width=96) (actual time=0.14..1.39 rows=21 loops=1)
-> Index Scan Backward using big_table_pkey on big_table k (cost=0.00..1461.15 rows=19286 width=52) (actual
time=0.07..0.47rows=21 loops=1)
-> Index Scan using 4000rows_table_pkey on 4000rows_table zs (cost=0.00..5.97 rows=1 width=44) (actual
time=0.02..0.02rows=0 loops=21)
2. After analyze uses hashjoins
When I remove this order by limit clause the query after analyze takes
the same time and the query before analyze is much more slower.
I won't blame the planer. How he could learn that he should first
take those 20 rows and than perform joins? There is a where clause
with complex exists(subquery) condition regarding one of big_table fields,
but removing this condition does not change the query plan.
Pure joining without any additional conditions and only primary key of big
table in select clause runs 4 times slower then whole query before
vacuuum analyze :)
Does in all the planer take in the consideration the limit clause?
Probably I'm missing something. I don't know much about the planer.
Finaly I have redesigned the query.
Regards,
Jacek
On Mon, 11 Aug 2003 14:25:03 -0400 Tom Lane <tgl@sss.pgh.pa.us> wrote: => set enable_mergejoin to off; => explain analyze ... query ... => => If it finishes in a reasonable amount of time, send the explain output. Hi again, I did this on the 7.3.1 database, and attached the output. It actually ran faster after ANALYZE and 'set enable_mergejoin to off' Thanks! I also reloaded this database onto 7.3.4, tried the same query after the ANALYZE, and the query executed a lot faster. Thanks again! I also attached the output of the EXPLAIN ANALYZE on 7.3.4 For now I'll maybe just disable mergejoin. But definitely a postgres upgrade is what I will do. I went through the different outputs of EXPLAIN ANALYZE a bit, and I think I can now see where the difference is. Thanks a lot for the help. Regards Stefan.