pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedDifference between 8.1 & 8.3
3+ messages / 2 participants
[nested] [flat]
* Difference between 8.1 & 8.3
@ 2008-07-16 21:37 Patrick Vachon <pvachon.videotron@videotron.ca>
0 siblings, 1 reply; 3+ messages in thread
From: Patrick Vachon @ 2008-07-16 21:37 UTC (permalink / raw)
To: pgsql-performance
Hi guys,
I've got a query that is running slower on 8.3 than on 8.1 (with
equivalent server config),
because the join ordering is not the same, at least that's my guess... ;-)
In 8.1.4, table A had 122880 pages, B 112690 pages and C 80600 pages.
Now in 8.3.3, table A has only 77560 pages, B 69580 but C remains at
80600 pages.
In 8.1 the tables were joined in that way (using explain analyse):
C join A join B
now in 8.3:
B join A join C
Beside that, the plan is very similar, but the indexes used are not the
same.
Could the number of disk pages of a table influence the
order in which it is joined, even when it is scanned with an index?
I'm pretty sure it is because of the reduced table sizes,
since the server configuration is the same.
Thoughts?
Thanks,
Patrick
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Difference between 8.1 & 8.3
@ 2008-07-16 21:59 Gregory Williamson <Gregory.Williamson@digitalglobe.com>
parent: Patrick Vachon <pvachon.videotron@videotron.ca>
0 siblings, 1 reply; 3+ messages in thread
From: Gregory Williamson @ 2008-07-16 21:59 UTC (permalink / raw)
To: Patrick Vachon <pvachon.videotron@videotron.ca>; pgsql-performance
Patrick Vachon wrote:
> Hi guys,
>
> I've got a query that is running slower on 8.3 than on 8.1 (with
> equivalent server config),
> because the join ordering is not the same, at least that's my guess... ;-)
>
> In 8.1.4, table A had 122880 pages, B 112690 pages and C 80600 pages.
> Now in 8.3.3, table A has only 77560 pages, B 69580 but C remains at
> 80600 pages.
>
> In 8.1 the tables were joined in that way (using explain analyse):
> C join A join B
> now in 8.3:
> B join A join C
> Beside that, the plan is very similar, but the indexes used are not the
> same.
>
> Could the number of disk pages of a table influence the
> order in which it is joined, even when it is scanned with an index?
>
> I'm pretty sure it is because of the reduced table sizes,
> since the server configuration is the same.
>
> Thoughts?
8.3 has fewer automatic casts to text types; perhaps you have indexes which are not being used because of mismatched types ? Perhaps an EXPLAIN ANALYZE from both, if possible, would clairfy.
HTH,
Greg Williamson
Senior DBA
DigitalGlobe
Confidentiality Notice: This e-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information and must be protected in accordance with those provisions. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.
(My corporate masters made me say this.)
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Difference between 8.1 & 8.3
@ 2008-07-22 15:15 Patrick Vachon <pvachon.videotron@videotron.ca>
parent: Gregory Williamson <Gregory.Williamson@digitalglobe.com>
0 siblings, 0 replies; 3+ messages in thread
From: Patrick Vachon @ 2008-07-22 15:15 UTC (permalink / raw)
To: Gregory Williamson <Gregory.Williamson@digitalglobe.com>; +Cc: pgsql-performance
Hi,
Finally found the problem.
Turning off nested loops gave me much better performance on 8.3 than 8.1.
The problem seems to come from postgresql miscalculation of the number
of rows returned by nested loops.
It is well described in that thread:
http://archives.postgresql.org/pgsql-performance/2008-03/msg00371.php
In my case, it was by a factor of 20000.
Of course, I can't turn off nested loop in my database,
it will impact performance on small tables too much...
So there is no easy fix for that, it seems,
beside playing with per-column statistics-gathering target maybe?
Patrick
Gregory Williamson wrote:
>
> Patrick Vachon wrote:
>
> > Hi guys,
> >
> > I've got a query that is running slower on 8.3 than on 8.1 (with
> > equivalent server config),
> > because the join ordering is not the same, at least that's my
> guess... ;-)
> >
> > In 8.1.4, table A had 122880 pages, B 112690 pages and C 80600 pages.
> > Now in 8.3.3, table A has only 77560 pages, B 69580 but C remains at
> > 80600 pages.
> >
> > In 8.1 the tables were joined in that way (using explain analyse):
> > C join A join B
> > now in 8.3:
> > B join A join C
> > Beside that, the plan is very similar, but the indexes used are not the
> > same.
> >
> > Could the number of disk pages of a table influence the
> > order in which it is joined, even when it is scanned with an index?
> >
> > I'm pretty sure it is because of the reduced table sizes,
> > since the server configuration is the same.
> >
> > Thoughts?
>
> 8.3 has fewer automatic casts to text types; perhaps you have indexes
> which are not being used because of mismatched types ? Perhaps an
> EXPLAIN ANALYZE from both, if possible, would clairfy.
>
> HTH,
>
> Greg Williamson
> Senior DBA
> DigitalGlobe
>
> Confidentiality Notice: This e-mail message, including any
> attachments, is for the sole use of the intended recipient(s) and may
> contain confidential and privileged information and must be protected
> in accordance with those provisions. Any unauthorized review, use,
> disclosure or distribution is prohibited. If you are not the intended
> recipient, please contact the sender by reply e-mail and destroy all
> copies of the original message.
>
> (My corporate masters made me say this.)
>
> ------------------------------------------------------------------------
>
> No virus found in this incoming message.
> Checked by AVG.
> Version: 7.5.524 / Virus Database: 270.5.0/1558 - Release Date: 7/17/2008 9:56 AM
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2008-07-22 15:15 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-07-16 21:37 Difference between 8.1 & 8.3 Patrick Vachon <pvachon.videotron@videotron.ca>
2008-07-16 21:59 ` Gregory Williamson <Gregory.Williamson@digitalglobe.com>
2008-07-22 15:15 ` Patrick Vachon <pvachon.videotron@videotron.ca>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox