Re: Surprise :-(
От | Nigel J. Andrews |
---|---|
Тема | Re: Surprise :-( |
Дата | |
Msg-id | Pine.LNX.4.21.0209100012430.619-100000@ponder.fairway2k.co.uk обсуждение исходный текст |
Ответ на | Re: Surprise :-( ("Mihai Gheorghiu" <tanethq@earthlink.net>) |
Список | pgsql-general |
On Mon, 9 Sep 2002, Mihai Gheorghiu wrote: > This is the result of the statistic/count query: > trxtype | count > ---------+-------- > MP | 347529 > ... > RG | 30438 > ... > > > >On Fri, 6 Sep 2002, Mihai Gheorghiu wrote: > > > >> I ran select from pg_statistics... as you advised > >> The result is attached. > >> Col# Name > >> 5 account > >> 10 trxtype > >> 15 amount > >> 28 isposted starelid,staattnum,staop,stanullfrac,stacommonfrac,stacommonval,staloval,stahival ---------,----------,------,------------,--------------,--------------------------,--------------------------,----------------------------------- 55256329,10,1058,0,0.0479733,RG,AS,XP RG fraction of table = 30 / 700 = 5% (as pg_statistics shows) MP fraction of table = 350 / 700 = 50% (expected in pg_statistics) May be someone else can verify that 7.1.3 had problems with stats gathering but all this just suggests to me that the table hasn't been analysed. Are you sure it was done? Try what I suggest below, to do just the one table, when you have time and if it's still appropiate. Also you could run the command: UPDATE pg_statistics SET sttcommonval = 'MP', stacommonfrac = 0.5 WHERE statrelid = 55256329 AND staattnum = 10; which will make pg_statistics reflect reality more accurate. However, that is just going to make the planner choose the seqscan plan over the index scan and doesn't explain why the statistics weren't updated properly by your vacuum analyze. Are you also sure that this same query on the same data ran faster before the vacuum? Perhaps the fast query used a different value in the trxtype test? It boils down to something must have changed and if it's not the data it must be the query. [as a reminder...] > >> >> > >> >> explain select account, sum(amount) from tbas_transactions where > isposted > >> >> and trxtype = 'MP' group by account; > >> >> > >> >> Sorry, I do not have an explain from before vacuum analyze. > >> >> The table has ~700k rows and indices on account, trxtype and a few > other > >> >> fields used in other queries. > > And an interesting item to perform... > >However, as it stands I suggest you should do a > > > > VACUUM VERBOSE ANALYZE tbas_transactions > > > >and retest your slow query. If no significant improvement it would be a > good > >idea to show us the output of that vacuum command and the same pg_statistic > >entries as before but taken after this vacuum. > > I hope the 7.2.2 install is going well. It will be interesting see what you make of it regarding this issue. I can't see it taking a significantly shorter time for this query on this data. -- Nigel J. Andrews Director --- Logictree Systems Limited Computer Consultants
В списке pgsql-general по дате отправления:
Предыдущее
От: Martijn van OosterhoutДата:
Сообщение: Re: Creating tons of tables to support a query