| From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
|---|---|
| To: | kaloyan(at)digsys(dot)bg |
| Cc: | pgsql-performance(at)postgresql(dot)org |
| Subject: | Re: Slow query after upgrade from 8.2 to 8.4 |
| Date: | 2011-12-09 15:30:17 |
| Message-ID: | 27472.1323444617@sss.pgh.pa.us |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-performance |
Kaloyan Iliev Iliev <kaloyan(at)digsys(dot)bg> writes:
> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
> <html>
> <head>
> <meta content="text/html;charset=windows-1251"
> http-equiv="Content-Type">
> </head>
> <body bgcolor="#ffffff" text="#000000">
> <tt>Hi,<br>
> Actually I think the problem is with this sub query:<br>
> explain analyze select 1<br>
> from acc_clients AC,<br>
> acc_debts AD,<br>
> debts_desc DD,<br>
> config CF<br>
> where AC.ino = 1200000 AND<br>
> <br>
> CF.id = (select id<br>
> from config<br>
> where
> confid=CF.confid ORDER BY archived_at DESC LIMIT 1) AND<br>
> AD.transact_no =
> AC.transact_no AND<br>
> AD.debtid = DD.debtid AND<br>
> CF.office = 18 AND<br>
> DD.refid = CF.confid LIMIT
> 1;</tt><br>
> <br>
> Instead of starting from '<tt>AC.ino = 1200000' and limit the rows IT
> start with '</tt><tt>CF.office = 18' which returns much more rows:<br>
Please don't post HTML mail.
I think the real issue is that you've got an astonishingly expensive
approach to keeping obsolete "config" rows around. You should get rid
of that "ORDER BY archived_at" sub-select, either by not storing
obsolete rows at all (you could move them to a history table instead),
or by marking valid rows with a boolean flag.
However, it's not apparent to me why you would see any difference
between 8.2 and 8.4 on this type of query. I tried a query analogous
to this one on both, and got identical plans. I'm guessing that your
slowdown is due to not having updated statistics on the new
installation, or perhaps failing to duplicate some relevant settings.
regards, tom lane
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Daniel Cristian Cruz | 2011-12-10 16:52:09 | Common slow query reasons - help with a special log |
| Previous Message | Kaloyan Iliev Iliev | 2011-12-09 12:23:35 | Re: Slow query after upgrade from 8.2 to 8.4 |