Обсуждение: Postgres 8.3, four times slower queries?

Hi,

We are currently running postgres 8.2 and are evaluating the upgrade to 8.3.
Some of our tests are indicating that postgresql 8.3 is actually degrading the
performance of some of our queries by a factor of 10 or more.  The queries in
question are selects that are heavy on joins (~10 tables) with a lot of
timestamp-based conditions in where clauses.  The tables and queries are tuned,
that is, there is no issue with the table structure, or missing indexes.  This
is a side-by-side query performance measurement between 8.2 and 8.3 with an
identical dataset and schema.


                              8.2.12             8.3.3
                            Time (ms)        Time (ms)
                            1st   2nd        1st   2nd
                            time  time       time  time

Query 1                     759   130        3294  1758

attached you will find the explain analyze for this query.  Any insight into
this issue would be very appreciated.  Thanks.

Aaron Guyon <battlemage@gmail.com> writes:
> We are currently running postgres 8.2 and are evaluating the upgrade to 8.3.
> Some of our tests are indicating that postgresql 8.3 is actually degrading
> the performance of some of our queries by a factor of 10 or more.

Are you sure you are comparing apples to apples here?  Same configure
options for the builds, same parameter values in postgresql.conf, both
databases ANALYZEd, etc?  And are they running on the same hardware?

The rowcount estimates seem to be a bit different, which might account
for the difference in plan choices, but I'm not convinced that that is
the reason for the slowness.  The parts of the plans that are exactly
comparable show very significant speed differences, eg

>              ->  Index Scan using idx_skin_day_part_id on skin t2  (cost=0.00..6.28 rows=1 width=24) (actual
time=2.484..2.486rows=1 loops=7) 
>                    Index Cond: (t2.day_part_id = t10.id)
>                    Filter: (t2.active <> 0::numeric)
>        ->  Index Scan using idx_skin_slot_skin_id on skin_slot t11  (cost=0.00..6.54 rows=92 width=25) (actual
time=12.726..276.412rows=94 loops=4) 
>              Index Cond: (t11.skin_id = t2.id)
>              Filter: (t11.active <> 0::numeric)

>              ->  Index Scan using idx_skin_day_part_id on skin t2  (cost=0.00..6.28 rows=1 width=30) (actual
time=0.028..0.031rows=1 loops=7) 
>                    Index Cond: (t2.day_part_id = t10.id)
>                    Filter: (active <> 0::numeric)
>        ->  Index Scan using idx_skin_slot_skin_id on skin_slot t11  (cost=0.00..6.85 rows=93 width=30) (actual
time=0.053..1.382rows=94 loops=4) 
>              Index Cond: (t2.id = t11.skin_id)
>              Filter: (active <> 0::numeric)

There's nothing in 8.3 vs 8.2 to explain that, if they're configured
the same and running in the same environment.

            regards, tom lane

On Mon, Mar 2, 2009 at 10:23 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Thank you for looking at this Tom.  Yes, we have made sure we are comparing
apples to apples here.  The postgresql.confs are identical, as are the configure flags:However, the db was not analyzed.  I'll attached the new explain analyze of the queries with the db analyzed, but 8.2 still beats 8.3.

The tests are both being run on the same machine, a Quad-core AMD Opteron Processor 2212
(each with 1024 KB cache) and 4GB of RAM.

I find it telling that the query plan differs so much between postgres 8.2. and
8.3.  For example, why does the 8.3. planner choose to perform so many seq
scans?  I know seq scans are faster than index scans for small tables, but
these tables have 60K+ rows... surely an index scan would have been a better
choice here?  If you look at the 8.2. query plan, it is very clean in
comparison, index scans all the way through.  I can't help but think the 8.3
planner is simply failing to make the right choices in our case. Another
question would be, why are there so many hash joins in the 8.3 plan now?  All
our indexes are btrees...

Any light that can be shed on what going on with the 8.3. planner would be much
appreciated.  Thanks in advance.

On Tue, 3 Mar 2009, Aaron Guyon wrote:

> On Mon, Mar 2, 2009 at 10:23 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
>> Are you sure you are comparing apples to apples here?  Same configure
>> options for the builds, same parameter values in postgresql.conf, both
>> databases ANALYZEd, etc?  And are they running on the same hardware?
>>
>
> Thank you for looking at this Tom.  Yes, we have made sure we are comparing
> apples to apples here.  The postgresql.confs are identical, as are the
> configure flags:
> --disable-debug --enable-shared --enable-thread-safety --with-perl
> --with-pam --without-docdir --without-tcl --without-python --without-krb5
> --without-ldap --without-bonjour --enable-integer-datetimes
> --prefix=/opt/postgresql
>
> However, the db was not analyzed.  I'll attached the new explain analyze of
> the queries with the db analyzed, but 8.2 still beats 8.3.
>
> The tests are both being run on the same machine, a Quad-core AMD Opteron
> Processor 2212
> (each with 1024 KB cache) and 4GB of RAM.
>
> I find it telling that the query plan differs so much between postgres 8.2.
> and
> 8.3.  For example, why does the 8.3. planner choose to perform so many seq
> scans?  I know seq scans are faster than index scans for small tables, but
> these tables have 60K+ rows... surely an index scan would have been a better
> choice here?  If you look at the 8.2. query plan, it is very clean in
> comparison, index scans all the way through.  I can't help but think the 8.3
> planner is simply failing to make the right choices in our case. Another
> question would be, why are there so many hash joins in the 8.3 plan now?
> All
> our indexes are btrees...
>
> Any light that can be shed on what going on with the 8.3. planner would be
> much
> appreciated.  Thanks in advance.

if you haven't done a vaccum analyse on either installation then postgres'
idea of what sort of data is in the database is unpredictable, and as a
result it's not surprising that the two systems guess differently about
what sort of plan is going to be most efficiant.

try doing vaccum analyse on both databases and see what the results are.

David Lang

On Tue, Mar 3, 2009 at 12:38 PM, <david@lang.hm> wrote:

These are the results with vacuum analyze:
8.2.12: 624.366 ms
8.3.3: 1273.601 ms

Aaron Guyon <battlemage@gmail.com> writes:
> I find it telling that the query plan differs so much between postgres 8.2.

Well, you haven't shown us either the query or the table definitions,
so we're just guessing in the dark.  However, the occurrences of
"::numeric" in the query plan make me wonder whether all of your join
keys are numeric type.  If so, the reason 8.2 didn't use any hash joins
is that it couldn't --- it didn't have a hash method for numerics.  8.3
does and therefore has more flexibility of plan choice.  Comparisons on
numerics aren't terribly fast though (in either release).  I wonder
whether you could change the key columns to int or bigint.

I also find it a tad fishy that both releases are choosing *exactly* the
same join order when there is hardly anything else that is identical
about the plans --- given the cross-release variance in rowcount
estimates etc I'd have expected at least one difference.  Are you doing
something to force the join order, like running with a small
join_collapse_limit setting?  If so maybe you shouldn't.

            regards, tom lane

On Tue, Mar 3, 2009 at 5:34 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

No, we left the join_collapse_limit to the default 8.  We tried a higher value, but there was no difference in performance.

I'll post the query and the table descriptions in separate messages to the list to avoid my mail from being rejected for exceeding the size limit :)

Query and first part of the table descriptions

2nd part of table descriptions

On Wed, Mar 4, 2009 at 6:20 PM, Aaron Guyon <battlemage@gmail.com> wrote:
> On Tue, Mar 3, 2009 at 5:34 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>
>> Comparisons on
>> numerics aren't terribly fast though (in either release).  I wonder
>> whether you could change the key columns to int or bigint.
>
> I changed the affected columns from numeric to integers and I was unable to
> get any performance gain:
> 8.3.3: 1195 ms
> 8.2.12: 611 ms
>
> I've attached the new query plans.
>
>> Are you doing
>> something to force the join order, like running with a small
>> join_collapse_limit setting?  If so maybe you shouldn't.
>
> No, we left the join_collapse_limit to the default 8.  We tried a higher
> value, but there was no difference in performance.
>
> I'll post the query and the table descriptions in separate messages to the
> list to avoid my mail from being rejected for exceeding the size limit :)

Well, it looks like the problem is that 8.3 is not using the index
idx_bundle_content_bundle_id.  But I don't know why that should be
happening, unless there's a problem with that index.

...Robert

On Thu, Mar 5, 2009 at 10:20 AM, Kevin Grittner
<Kevin.Grittner@wicourts.gov> wrote:
>>>> Robert Haas <robertmhaas@gmail.com> wrote:
>> Well, it looks like the problem is that 8.3 is not using the index
>> idx_bundle_content_bundle_id.  But I don't know why that should be
>> happening, unless there's a problem with that index.
>
> I didn't see that index defined.  In fact, in the query shown, t8 is
> the payment_amount table, but in plan, I don't see any references to
> that table, and t8 is a table called bundle_content which is not
> included.

Good point.  Now that you mention it, I notice that many of the tables
and columns seem to have been renamed.  It's pretty hard to make
anything intelligible out of a schema that doesn't resemble the plan.

...Robert

Matching query plans with numerics changed to integers.

I sent the wrong query plans earlier

8.3.3: 1195 ms
8.2.12: 611 ms

>>> Aaron Guyon <battlemage@gmail.com> wrote:
> 8.3.3: 1195 ms
> 8.2.12: 611 ms

Could you send the non-commented lines from the postgresql.conf files
from both installations?

If feasible, update to the latest bug-fix version of 8.3.

Also, if you haven't already done so, try setting effective_cache_size
= '3GB' and random_page_cost = 2 for the 8.3 database and restart to
see what kind of plan you get.

-Kevin

On Thu, Mar 5, 2009 at 1:57 PM, Kevin Grittner
<Kevin.Grittner@wicourts.gov> wrote:
>>>> Aaron Guyon <battlemage@gmail.com> wrote:
>> 8.3.3: 1195 ms
>> 8.2.12: 611 ms
>
> Could you send the non-commented lines from the postgresql.conf files
> from both installations?
>
> If feasible, update to the latest bug-fix version of 8.3.
>
> Also, if you haven't already done so, try setting effective_cache_size
> = '3GB' and random_page_cost = 2 for the 8.3 database and restart to
> see what kind of plan you get.

I still there's a problem with that index, now it's called
idx_payment_amount_payment_id.  What do you get from this?

select * from pg_index where indexrelid =
'idx_payment_amount_payment_id'::regclass;

Have you tried:

reindex table payment_amount

...Robert

On Thu, Mar 5, 2009 at 12:58 PM, Joshua D. Drake <jd@commandprompt.com> wrote:

Right now, I'm getting very good results with the above.  I'm still running additional tests but I'll keep you guys updated. I've attached the new explain analyze.

On Thu, Mar 5, 2009 at 12:58 PM, Joshua D. Drake <jd@commandprompt.com> wrote:

These three are pretty much the same in terms of performance.  I stayed with the first one (cpu_tuple_cost = 0.5 and cpu_index_tuple_cost = 0.5).  As shown earlier, it gives a result similar or slightly better than 8.2.12 in terms of performance and response time.  The explain analyze shows that the query no longer causes postgreSQL to uses hashes, but indexes instead which boosted the performance of the query from ~1200 ms to ~600 ms.

Thank you everyone for all the help and feedback on this issue.