pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgres 8.3, four times slower queries?
16+ messages / 5 participants
[nested] [flat]
* Postgres 8.3, four times slower queries?
@ 2009-03-03 00:18 Aaron Guyon <battlemage@gmail.com>
0 siblings, 1 reply; 16+ messages in thread
From: Aaron Guyon @ 2009-03-03 00:18 UTC (permalink / raw)
To: pgsql-performance
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.
Attachments:
analyze_8_3_3.txt (text/plain, 6.9K) download
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=8251.47..8251.48 rows=1 width=37) (actual time=3294.104..3294.104 rows=0 loops=1)
-> Sort (cost=8251.47..8251.48 rows=1 width=37) (actual time=3294.096..3294.096 rows=0 loops=1)
Sort Key: t8.id
Sort Method: quicksort Memory: 17kB
-> Hash Join (cost=5509.94..8251.46 rows=1 width=37) (actual time=3294.030..3294.030 rows=0 loops=1)
Hash Cond: (t9.id = t8.content_id)
Join Filter: ((t2.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)) OR (t3.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)) OR (t6.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)) OR (t7.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)) OR (t8.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)) OR (t9.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)) OR (t10.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)) OR (t11.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)))
-> Seq Scan on content t9 (cost=0.00..2272.26 rows=68026 width=16) (actual time=3.891..377.945 rows=68045 loops=1)
-> Hash (cost=5498.91..5498.91 rows=882 width=93) (actual time=2625.501..2625.501 rows=1104 loops=1)
-> Hash Join (cost=3590.26..5498.91 rows=882 width=93) (actual time=2241.886..2620.141 rows=1104 loops=1)
Hash Cond: (t8.bundle_id = t6.id)
-> Seq Scan on bundle_content t8 (cost=0.00..1638.89 rows=69589 width=45) (actual time=5.160..334.263 rows=69606 loops=1)
-> Hash (cost=3579.17..3579.17 rows=887 width=56) (actual time=1998.681..1998.681 rows=1104 loops=1)
-> Hash Join (cost=1317.36..3579.17 rows=887 width=56) (actual time=1367.063..1993.592 rows=1104 loops=1)
Hash Cond: (t6.schedule_id = t7.id)
-> Seq Scan on bundle t6 (cost=0.00..2023.34 rows=61227 width=24) (actual time=3.785..390.578 rows=60919 loops=1)
Filter: (active <> 0::numeric)
-> Hash (cost=1314.41..1314.41 rows=236 width=48) (actual time=1355.121..1355.121 rows=332 loops=1)
-> Nested Loop (cost=490.86..1314.41 rows=236 width=48) (actual time=256.077..1353.495 rows=332 loops=1)
-> Hash Join (cost=490.86..628.39 rows=181 width=49) (actual time=240.596..1274.582 rows=336 loops=1)
Hash Cond: (t11.slot_id = t3.id)
-> Nested Loop (cost=0.00..131.76 rows=288 width=33) (actual time=92.007..1149.886 rows=376 loops=1)
-> Nested Loop (cost=0.00..77.95 rows=7 width=24) (actual time=41.217..41.425 rows=4 loops=1)
-> Index Scan using idx_day_part_du on day_part t10 (cost=0.00..8.73 rows=11 width=17) (actual time=23.893..23.926 rows=7 loops=1)
Index Cond: (display_unit_id = 250893::numeric)
Filter: (active <> 0::numeric)
-> 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.486 rows=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.412 rows=94 loops=4)
Index Cond: (t11.skin_id = t2.id)
Filter: (t11.active <> 0::numeric)
-> Hash (cost=380.45..380.45 rows=8833 width=16) (actual time=121.457..121.457 rows=8950 loops=1)
-> Seq Scan on loop_slot t3 (cost=0.00..380.45 rows=8833 width=16) (actual time=5.744..84.111 rows=8950 loops=1)
Filter: (active <> 0::numeric)
-> Index Scan using idx_schedule_owner_resource_id on schedule t7 (cost=0.00..3.78 rows=1 width=24) (actual time=0.216..0.220 rows=1 loops=336)
Index Cond: (t7.owner_resource_id = t3.id)
Filter: (t7.active <> 0::numeric)
Total runtime: 3294.712 ms
(39 rows)
analyze_8_2_12.txt (text/plain, 6.0K) download
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=10675.55..10675.56 rows=2 width=52) (actual time=758.259..758.259 rows=0 loops=1)
-> Sort (cost=10675.55..10675.55 rows=2 width=52) (actual time=758.250..758.250 rows=0 loops=1)
Sort Key: t8.id
-> Nested Loop (cost=0.00..10675.54 rows=2 width=52) (actual time=758.055..758.055 rows=0 loops=1)
Join Filter: ((t2.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)) OR (t3.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)) OR (t6.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)) OR (t7.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)) OR (t8.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)) OR (t9.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)) OR (t10.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)) OR (t11.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)))
-> Nested Loop (cost=0.00..7966.45 rows=698 width=108) (actual time=113.843..458.416 rows=1104 loops=1)
-> Nested Loop (cost=0.00..5463.73 rows=703 width=59) (actual time=92.699..398.968 rows=1104 loops=1)
-> Nested Loop (cost=0.00..1287.57 rows=186 width=51) (actual time=39.570..128.132 rows=332 loops=1)
-> Nested Loop (cost=0.00..755.75 rows=135 width=54) (actual time=39.448..118.909 rows=336 loops=1)
-> Nested Loop (cost=0.00..98.83 rows=211 width=35) (actual time=19.050..27.403 rows=376 loops=1)
-> Nested Loop (cost=0.00..58.75 rows=5 width=27) (actual time=18.892..19.081 rows=4 loops=1)
-> Index Scan using idx_day_part_du on day_part t10 (cost=0.00..8.41 rows=8 width=20) (actual time=18.739..18.775 rows=7 loops=1)
Index Cond: (display_unit_id = 250893::numeric)
Filter: (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.031 rows=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.382 rows=94 loops=4)
Index Cond: (t2.id = t11.skin_id)
Filter: (active <> 0::numeric)
-> Index Scan using loop_slot_pkey on loop_slot t3 (cost=0.00..3.10 rows=1 width=19) (actual time=0.226..0.229 rows=1 loops=376)
Index Cond: (t11.slot_id = t3.id)
Filter: (active <> 0::numeric)
-> Index Scan using idx_schedule_owner_resource_id on schedule t7 (cost=0.00..3.93 rows=1 width=30) (actual time=0.009..0.013 rows=1 loops=336)
Index Cond: (t3.id = t7.owner_resource_id)
Filter: (active <> 0::numeric)
-> Index Scan using idx_bundle_schedule_id on bundle t6 (cost=0.00..22.09 rows=29 width=30) (actual time=0.694..0.784 rows=3 loops=332)
Index Cond: (t6.schedule_id = t7.id)
Filter: (active <> 0::numeric)
-> Index Scan using idx_bundle_content_bundle_id on bundle_content t8 (cost=0.00..3.55 rows=1 width=60) (actual time=0.034..0.039 rows=1 loops=1104)
Index Cond: (t6.id = t8.bundle_id)
-> Index Scan using content_pkey on content t9 (cost=0.00..3.83 rows=1 width=19) (actual time=0.227..0.231 rows=1 loops=1104)
Index Cond: (t8.content_id = t9.id)
Total runtime: 759.001 ms
(34 rows)
* Re: Postgres 8.3, four times slower queries?
@ 2009-03-03 03:23 Tom Lane <tgl@sss.pgh.pa.us>
parent: Aaron Guyon <battlemage@gmail.com>
0 siblings, 1 reply; 16+ messages in thread
From: Tom Lane @ 2009-03-03 03:23 UTC (permalink / raw)
To: Aaron Guyon <battlemage@gmail.com>; +Cc: pgsql-performance
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.486 rows=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.412 rows=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.031 rows=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.382 rows=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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Postgres 8.3, four times slower queries?
@ 2009-03-03 17:31 Aaron Guyon <battlemage@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 4 replies; 16+ messages in thread
From: Aaron Guyon @ 2009-03-03 17:31 UTC (permalink / raw)
To: pgsql-performance
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.
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Postgres 8.3, four times slower queries?
@ 2009-03-03 17:38 david@lang.hm
parent: Aaron Guyon <battlemage@gmail.com>
3 siblings, 1 reply; 16+ messages in thread
From: david@lang.hm @ 2009-03-03 17:38 UTC (permalink / raw)
To: Aaron Guyon <battlemage@gmail.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Postgres 8.3, four times slower queries?
@ 2009-03-03 17:52 Aaron Guyon <battlemage@gmail.com>
parent: david@lang.hm
0 siblings, 0 replies; 16+ messages in thread
From: Aaron Guyon @ 2009-03-03 17:52 UTC (permalink / raw)
To: pgsql-performance
On Tue, Mar 3, 2009 at 12:38 PM, <david@lang.hm> wrote:
> 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
>
These are the results with vacuum analyze:
8.2.12: 624.366 ms
8.3.3: 1273.601 ms
Attachments:
analyze_8_2_12_va.txt (text/plain, 6.0K) download
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=9832.91..9832.92 rows=2 width=52) (actual time=623.808..623.808 rows=0 loops=1)
-> Sort (cost=9832.91..9832.92 rows=2 width=52) (actual time=623.799..623.799 rows=0 loops=1)
Sort Key: t8.id
-> Nested Loop (cost=0.00..9832.90 rows=2 width=52) (actual time=623.467..623.467 rows=0 loops=1)
Join Filter: ((t2.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)) OR (t3.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)) OR (t6.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)) OR (t7.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)) OR (t8.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)) OR (t9.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)) OR (t10.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)) OR (t11.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)))
-> Nested Loop (cost=0.00..7381.49 rows=598 width=108) (actual time=75.634..556.642 rows=1104 loops=1)
-> Nested Loop (cost=0.00..5087.76 rows=602 width=59) (actual time=71.744..523.690 rows=1104 loops=1)
-> Nested Loop (cost=0.00..1171.66 rows=165 width=51) (actual time=66.427..499.798 rows=332 loops=1)
-> Nested Loop (cost=0.00..684.77 rows=117 width=54) (actual time=38.266..440.024 rows=336 loops=1)
-> Nested Loop (cost=0.00..84.08 rows=182 width=35) (actual time=16.785..402.046 rows=376 loops=1)
-> Nested Loop (cost=0.00..52.33 rows=4 width=27) (actual time=11.355..11.547 rows=4 loops=1)
-> Index Scan using idx_day_part_du on day_part t10 (cost=0.00..8.28 rows=7 width=19) (actual time=0.713..0.748 rows=7 loops=1)
Index Cond: (display_unit_id = 250893::numeric)
Filter: (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=1.526..1.529 rows=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.81 rows=90 width=30) (actual time=1.370..96.925 rows=94 loops=4)
Index Cond: (t2.id = t11.skin_id)
Filter: (active <> 0::numeric)
-> Index Scan using loop_slot_pkey on loop_slot t3 (cost=0.00..3.29 rows=1 width=19) (actual time=0.083..0.087 rows=1 loops=376)
Index Cond: (t11.slot_id = t3.id)
Filter: (active <> 0::numeric)
-> Index Scan using idx_schedule_owner_resource_id on schedule t7 (cost=0.00..4.15 rows=1 width=30) (actual time=0.154..0.163 rows=1 loops=336)
Index Cond: (t3.id = t7.owner_resource_id)
Filter: (active <> 0::numeric)
-> Index Scan using idx_bundle_schedule_id on bundle t6 (cost=0.00..23.37 rows=29 width=30) (actual time=0.025..0.040 rows=3 loops=332)
Index Cond: (t6.schedule_id = t7.id)
Filter: (active <> 0::numeric)
-> Index Scan using idx_bundle_content_bundle_id on bundle_content t8 (cost=0.00..3.80 rows=1 width=60) (actual time=0.011..0.015 rows=1 loops=1104)
Index Cond: (t6.id = t8.bundle_id)
-> Index Scan using content_pkey on content t9 (cost=0.00..4.05 rows=1 width=19) (actual time=0.013..0.017 rows=1 loops=1104)
Index Cond: (t8.content_id = t9.id)
Total runtime: 624.366 ms
(34 rows)
analyze_8_3_3_va.txt (text/plain, 6.7K) download
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=7384.05..7384.06 rows=1 width=37) (actual time=1273.060..1273.060 rows=0 loops=1)
-> Sort (cost=7384.05..7384.06 rows=1 width=37) (actual time=1273.052..1273.052 rows=0 loops=1)
Sort Key: t8.id
Sort Method: quicksort Memory: 17kB
-> Nested Loop (cost=3303.53..7384.04 rows=1 width=37) (actual time=1272.976..1272.976 rows=0 loops=1)
Join Filter: ((t2.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)) OR (t3.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)) OR (t6.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)) OR (t7.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)) OR (t8.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)) OR (t9.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)) OR (t10.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)) OR (t11.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)))
-> Hash Join (cost=3303.53..5208.77 rows=515 width=93) (actual time=863.416..1209.038 rows=1104 loops=1)
Hash Cond: (t8.bundle_id = t6.id)
-> Seq Scan on bundle_content t8 (cost=0.00..1639.06 rows=69606 width=45) (actual time=0.077..272.391 rows=69606 loops=1)
-> Hash (cost=3297.06..3297.06 rows=518 width=56) (actual time=651.968..651.968 rows=1104 loops=1)
-> Hash Join (cost=1040.33..3297.06 rows=518 width=56) (actual time=127.291..646.867 rows=1104 loops=1)
Hash Cond: (t6.schedule_id = t7.id)
-> Seq Scan on bundle t6 (cost=0.00..2023.55 rows=60798 width=24) (actual time=0.055..273.351 rows=60919 loops=1)
Filter: (active <> 0::numeric)
-> Hash (cost=1038.54..1038.54 rows=143 width=48) (actual time=124.746..124.746 rows=332 loops=1)
-> Nested Loop (cost=77.89..1038.54 rows=143 width=48) (actual time=46.253..123.164 rows=332 loops=1)
-> Hash Join (cost=77.89..563.45 rows=105 width=49) (actual time=42.417..109.635 rows=336 loops=1)
Hash Cond: (t3.id = t11.slot_id)
-> Seq Scan on loop_slot t3 (cost=0.00..380.55 rows=9241 width=16) (actual time=0.049..42.454 rows=8950 loops=1)
Filter: (active <> 0::numeric)
-> Hash (cost=75.90..75.90 rows=159 width=33) (actual time=32.526..32.526 rows=376 loops=1)
-> Nested Loop (cost=0.00..75.90 rows=159 width=33) (actual time=21.187..30.820 rows=376 loops=1)
-> Nested Loop (cost=0.00..45.17 rows=4 width=24) (actual time=15.635..15.847 rows=4 loops=1)
-> Index Scan using idx_day_part_du on day_part t10 (cost=0.00..7.42 rows=6 width=16) (actual time=5.379..5.413 rows=7 loops=1)
Index Cond: (display_unit_id = 250893::numeric)
Filter: (active <> 0::numeric)
-> Index Scan using idx_skin_day_part_id on skin t2 (cost=0.00..6.28 rows=1 width=24) (actual time=1.474..1.477 rows=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.52 rows=93 width=25) (actual time=1.416..3.037 rows=94 loops=4)
Index Cond: (t11.skin_id = t2.id)
Filter: (t11.active <> 0::numeric)
-> Index Scan using idx_schedule_owner_resource_id on schedule t7 (cost=0.00..4.51 rows=1 width=24) (actual time=0.022..0.026 rows=1 loops=336)
Index Cond: (t7.owner_resource_id = t3.id)
Filter: (t7.active <> 0::numeric)
-> Index Scan using content_pkey on content t9 (cost=0.00..4.17 rows=1 width=16) (actual time=0.011..0.015 rows=1 loops=1104)
Index Cond: (t9.id = t8.content_id)
Total runtime: 1273.601 ms
(38 rows)
* Re: Postgres 8.3, four times slower queries?
@ 2009-03-03 22:34 Tom Lane <tgl@sss.pgh.pa.us>
parent: Aaron Guyon <battlemage@gmail.com>
3 siblings, 1 reply; 16+ messages in thread
From: Tom Lane @ 2009-03-03 22:34 UTC (permalink / raw)
To: Aaron Guyon <battlemage@gmail.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Postgres 8.3, four times slower queries?
@ 2009-03-04 23:20 Aaron Guyon <battlemage@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 3 replies; 16+ messages in thread
From: Aaron Guyon @ 2009-03-04 23:20 UTC (permalink / raw)
To: pgsql-performance
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 :)
Attachments:
analyze_8_2_12_ints.txt (text/plain, 6.1K) download
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=6986.01..6986.02 rows=1 width=20) (actual time=611.061..611.061 rows=0 loops=1)
-> Sort (cost=6986.01..6986.02 rows=1 width=20) (actual time=611.053..611.053 rows=0 loops=1)
Sort Key: t8.id
-> Nested Loop (cost=955.90..6986.00 rows=1 width=20) (actual time=610.942..610.942 rows=0 loops=1)
Join Filter: ((t2.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)) OR (t3.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)) OR (t6.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)) OR (t7.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)) OR (t8.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)) OR (t9.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)) OR (t10.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)) OR (t11.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)))
-> Nested Loop (cost=955.90..4989.27 rows=523 width=76) (actual time=28.274..548.664 rows=1104 loops=1)
-> Hash Join (cost=955.90..3195.56 rows=526 width=52) (actual time=28.123..520.620 rows=1104 loops=1)
Hash Cond: (t6.schedule_id = t7.id)
-> Seq Scan on bundle t6 (cost=0.00..2002.69 rows=61789 width=16) (actual time=0.026..253.360 rows=60920 loops=1)
Filter: (active <> 0)
-> Hash (cost=954.15..954.15 rows=140 width=44) (actual time=25.795..25.795 rows=332 loops=1)
-> Nested Loop (cost=0.00..954.15 rows=140 width=44) (actual time=0.546..24.364 rows=332 loops=1)
-> Nested Loop (cost=0.00..562.62 rows=100 width=40) (actual time=0.471..15.759 rows=336 loops=1)
-> Nested Loop (cost=0.00..74.40 rows=156 width=28) (actual time=0.233..6.404 rows=376 loops=1)
-> Nested Loop (cost=0.00..45.04 rows=4 width=20) (actual time=0.157..0.324 rows=4 loops=1)
-> Index Scan using idx_day_part_du on day_part t10 (cost=0.00..7.30 rows=6 width=12) (actual time=0.076..0.108 rows=7 loops=1)
Index Cond: (display_unit_id = 250893)
Filter: (active <> 0)
-> Index Scan using idx_skin_day_part_id on skin t2 (cost=0.00..6.28 rows=1 width=16) (actual time=0.016..0.018 rows=1 loops=7)
Index Cond: (t2.day_part_id = t10.id)
Filter: (active <> 0)
-> Index Scan using idx_skin_slot_skin_id on skin_slot t11 (cost=0.00..6.09 rows=100 width=16) (actual time=0.030..0.817 rows=94 loops=4)
Index Cond: (t2.id = t11.skin_id)
Filter: (active <> 0)
-> Index Scan using loop_slot_pkey on loop_slot t3 (cost=0.00..3.12 rows=1 width=12) (actual time=0.007..0.011 rows=1 loops=376)
Index Cond: (t11.slot_id = t3.id)
Filter: (active <> 0)
-> Index Scan using idx_schedule_owner_resource_id on schedule t7 (cost=0.00..3.90 rows=1 width=16) (actual time=0.007..0.011 rows=1 loops=336)
Index Cond: (t3.id = t7.owner_resource_id)
Filter: (active <> 0)
-> Index Scan using idx_bundle_content_bundle_id on bundle_content t8 (cost=0.00..3.40 rows=1 width=28) (actual time=0.007..0.011 rows=1 loops=1104)
Index Cond: (t6.id = t8.bundle_id)
-> Index Scan using content_pkey on content t9 (cost=0.00..3.77 rows=1 width=12) (actual time=0.007..0.011 rows=1 loops=1104)
Index Cond: (t8.content_id = t9.id)
Total runtime: 611.680 ms
(35 rows)
analyze_8_3_3_ints.txt (text/plain, 6.6K) download
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=6891.89..6891.90 rows=1 width=20) (actual time=1194.811..1194.811 rows=0 loops=1)
-> Sort (cost=6891.89..6891.89 rows=1 width=20) (actual time=1194.803..1194.803 rows=0 loops=1)
Sort Key: t8.id
Sort Method: quicksort Memory: 17kB
-> Nested Loop (cost=3108.63..6891.88 rows=1 width=20) (actual time=1194.733..1194.733 rows=0 loops=1)
Join Filter: ((t2.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)) OR (t3.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)) OR (t6.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)) OR (t7.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)) OR (t8.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)) OR (t9.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)) OR (t10.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)) OR (t11.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)))
-> Hash Join (cost=3108.63..4816.57 rows=571 width=76) (actual time=799.114..1135.524 rows=1104 loops=1)
Hash Cond: (t8.bundle_id = t6.id)
-> Seq Scan on bundle_content t8 (cost=0.00..1441.17 rows=69617 width=28) (actual time=0.042..263.815 rows=69617 loops=1)
-> Hash (cost=3101.44..3101.44 rows=575 width=52) (actual time=593.227..593.227 rows=1104 loops=1)
-> Hash Join (cost=975.62..3101.44 rows=575 width=52) (actual time=98.739..588.389 rows=1104 loops=1)
Hash Cond: (t6.schedule_id = t7.id)
-> Seq Scan on bundle t6 (cost=0.00..1891.69 rows=60901 width=16) (actual time=0.035..249.993 rows=60920 loops=1)
Filter: (active <> 0)
-> Hash (cost=973.66..973.66 rows=157 width=44) (actual time=96.404..96.404 rows=332 loops=1)
-> Nested Loop (cost=84.27..973.66 rows=157 width=44) (actual time=23.020..94.924 rows=332 loops=1)
-> Hash Join (cost=84.27..539.32 rows=120 width=40) (actual time=22.881..86.225 rows=336 loops=1)
Hash Cond: (t3.id = t11.slot_id)
-> Seq Scan on loop_slot t3 (cost=0.00..362.56 rows=9129 width=12) (actual time=0.052..38.501 rows=8951 loops=1)
Filter: (active <> 0)
-> Hash (cost=81.97..81.97 rows=184 width=28) (actual time=13.379..13.379 rows=376 loops=1)
-> Nested Loop (cost=0.00..81.97 rows=184 width=28) (actual time=3.188..11.716 rows=376 loops=1)
-> Nested Loop (cost=0.00..51.98 rows=4 width=20) (actual time=0.309..0.499 rows=4 loops=1)
-> Index Scan using idx_day_part_du on day_part t10 (cost=0.00..7.96 rows=7 width=12) (actual time=0.181..0.215 rows=7 loops=1)
Index Cond: (display_unit_id = 250893)
Filter: (active <> 0)
-> Index Scan using idx_skin_day_part_id on skin t2 (cost=0.00..6.28 rows=1 width=16) (actual time=0.024..0.027 rows=1 loops=7)
Index Cond: (t2.day_part_id = t10.id)
Filter: (t2.active <> 0)
-> Index Scan using idx_skin_slot_skin_id on skin_slot t11 (cost=0.00..6.16 rows=107 width=16) (actual time=0.729..2.100 rows=94 loops=4)
Index Cond: (t11.skin_id = t2.id)
Filter: (t11.active <> 0)
-> Index Scan using idx_schedule_owner_resource_id on schedule t7 (cost=0.00..3.61 rows=1 width=16) (actual time=0.007..0.011 rows=1 loops=336)
Index Cond: (t7.owner_resource_id = t3.id)
Filter: (t7.active <> 0)
-> Index Scan using content_pkey on content t9 (cost=0.00..3.58 rows=1 width=12) (actual time=0.007..0.011 rows=1 loops=1104)
Index Cond: (t9.id = t8.content_id)
Total runtime: 1195.615 ms
(38 rows)
* Re: Postgres 8.3, four times slower queries?
@ 2009-03-04 23:42 Aaron Guyon <battlemage@gmail.com>
parent: Aaron Guyon <battlemage@gmail.com>
2 siblings, 0 replies; 16+ messages in thread
From: Aaron Guyon @ 2009-03-04 23:42 UTC (permalink / raw)
To: pgsql-performance
Query and first part of the table descriptions
Attachments:
note1.txt (text/plain, 9.4K) download
Query:
explain analyze
select distinct on
(t8.id) t8.id,
t8.payment_id,
t8.amount_id,
t8.active,
t8.organization_id
from
project t2,
project_invoice t3,
payment t6,
invoice t7,
payment_amount t8,
amount t9,
department t10,
project_type t11
where
t10.company_id = 250893
and t2.department_id = t10.id
and t3.id = t7.owner_resource_id
and t2.id = t11.project_id
and t11.slot_id = t3.id
and t6.invoice_id = t7.id
and t6.id = t8.payment_id
and t8.amount_id = t9.id
and t2.active <> 0
and t3.active <> 0
and t6.active <> 0
and t7.active <> 0
and t10.active <> 0
and t11.active <> 0
and (t2.not_modified_since >= to_timestamp(E'2009-02-24T11:19:57', E'YYYY/MM/DD HH:MI:SS')
or t3.not_modified_since >= to_timestamp(E'2009-02-24T11:19:57', E'YYYY/MM/DD HH:MI:SS')
or t6.not_modified_since >= to_timestamp(E'2009-02-24T11:19:57', E'YYYY/MM/DD HH:MI:SS')
or t7.not_modified_since >= to_timestamp(E'2009-02-24T11:19:57', E'YYYY/MM/DD HH:MI:SS')
or t8.not_modified_since >= to_timestamp(E'2009-02-24T11:19:57', E'YYYY/MM/DD HH:MI:SS')
or t9.not_modified_since >= to_timestamp(E'2009-02-24T11:19:57', E'YYYY/MM/DD HH:MI:SS')
or t10.not_modified_since >= to_timestamp(E'2009-02-24T11:19:57', E'YYYY/MM/DD HH:MI:SS')
or t11.not_modified_since >= to_timestamp(E'2009-02-24T11:19:57', E'YYYY/MM/DD HH:MI:SS'));
Table "public.payment"
Column | Type | Modifiers
-----------------------+-----------------------------+--------------------
id | integer | not null
name | character varying(255) |
active | integer | not null default 1
status | numeric | not null default 0
last_status_change | date |
not_modified_since | timestamp without time zone |
organization_id | integer |
added_user_id | numeric |
added_tm | timestamp without time zone |
edited_user_id | numeric |
edited_tm | timestamp without time zone |
folder_id | integer |
max_duration_msec | numeric |
tax_id | integer |
customer_id | integer |
allow_custom_duration | numeric |
loop_tax_id | integer |
loop_weight | numeric |
fullbill | numeric |
invoice_id | integer |
trigger_tax_id | integer |
Indexes:
"payment_pkey" PRIMARY KEY, btree (id)
"idx_payment_organization_id" btree (organization_id)
"idx_payment_lower_name" btree (lower(name::text))
"idx_payment_nms" btree (not_modified_since)
"idx_payment_invoice_id" btree (invoice_id)
Foreign-key constraints:
"payment_tax_id_fkey" FOREIGN KEY (tax_id) REFERENCES tax(id)
"payment_folder_id_fkey" FOREIGN KEY (folder_id) REFERENCES folder(id)
"payment_customer_id_fkey" FOREIGN KEY (customer_id) REFERENCES customer(id)
"payment_loop_tax_id_fkey" FOREIGN KEY (loop_tax_id) REFERENCES tax(id)
"payment_invoice_id_fkey" FOREIGN KEY (invoice_id) REFERENCES invoice(id)
"payment_trigger_tax_id_fkey" FOREIGN KEY (trigger_tax_id) REFERENCES tax(id)
Inherits: resource
Table "public.payment_amount"
Column | Type | Modifiers
--------------------+-----------------------------+--------------------
id | integer | not null
name | character varying(255) |
active | integer | not null default 1
status | numeric | not null default 0
last_status_change | date |
not_modified_since | timestamp without time zone |
organization_id | integer |
added_user_id | numeric |
added_tm | timestamp without time zone |
edited_user_id | numeric |
edited_tm | timestamp without time zone |
folder_id | integer |
payment_id | integer | not null
amount_id | integer | not null
Indexes:
"payment_amount_pkey" PRIMARY KEY, btree (id)
"index_payment_amount" UNIQUE, btree (payment_id, amount_id)
"idx_payment_amount_payment_id" btree (payment_id)
"idx_payment_amount_organization_id" btree (organization_id)
"idx_payment_amount_lower_name" btree (lower(name::text))
"idx_payment_amount_nms" btree (not_modified_since)
Foreign-key constraints:
"payment_amount_payment_id_fkey" FOREIGN KEY (payment_id) REFERENCES payment(id)
"payment_amount_amount_id_fkey" FOREIGN KEY (amount_id) REFERENCES amount(id)
Inherits: resource
Table "public.amount"
Column | Type | Modifiers
---------------------+-----------------------------+--------------------
id | integer | not null
name | character varying(255) |
active | integer | not null default 1
status | numeric | not null default 0
last_status_change | date |
not_modified_since | timestamp without time zone |
organization_id | integer |
added_user_id | numeric |
added_tm | timestamp without time zone |
edited_user_id | numeric |
edited_tm | timestamp without time zone |
folder_id | integer |
description | character varying(255) |
originalfilename | character varying(255) |
mime | character varying(255) | not null
checksum | character varying(255) |
customer_id | integer |
approval_status | numeric |
checksum2_type | numeric |
checksum2 | character varying(255) |
size | numeric |
archive_status | numeric | default 0
archived_on_utc | timestamp without time zone |
archived_by | integer |
approved_by_user_id | integer |
approved_on_utc | timestamp without time zone |
external_id | character varying(255) |
archive_priority | smallint | not null
Indexes:
"amount_pkey" PRIMARY KEY, btree (id)
"idx_amount_organization_id" btree (organization_id)
"idx_amount_lower_name" btree (lower(name::text))
"idx_amount_nms" btree (not_modified_since)
"idx_amount_not_null_external_id" btree (external_id) WHERE external_id IS NOT NULL
Foreign-key constraints:
"amount_approved_by_user_id_fkey" FOREIGN KEY (approved_by_user_id) REFERENCES users(id)
"amount_archived_by_fkey" FOREIGN KEY (archived_by) REFERENCES server_node(id)
"amount_folder_id_fkey" FOREIGN KEY (folder_id) REFERENCES folder(id)
"amount_customer_id_fkey" FOREIGN KEY (customer_id) REFERENCES customer(id)
Inherits: resource
Table "public.department"
Column | Type | Modifiers
----------------------+-----------------------------+-----------------------------------
id | integer | not null
name | character varying(255) |
active | integer | not null default 1
status | numeric | not null default 0
last_status_change | date |
not_modified_since | timestamp without time zone |
organization_id | integer |
added_user_id | numeric |
added_tm | timestamp without time zone |
edited_user_id | numeric |
edited_tm | timestamp without time zone |
folder_id | integer |
day_mask | numeric |
virtual_start_date | date | not null
virtual_end_date | date | not null
start_date | date |
end_date | date |
start_time | time without time zone | not null
end_time | time without time zone | not null
weight | numeric |
company_id | integer | not null
impressions_per_hour | numeric | not null default (- (1)::numeric)
minute_mask | text |
Indexes:
"department_pkey" PRIMARY KEY, btree (id)
"idx_department_organization_id" btree (organization_id)
"idx_department_du" btree (company_id)
"idx_department_lower_name" btree (lower(name::text))
"idx_department_nms" btree (not_modified_since)
Foreign-key constraints:
"company_id_fkey" FOREIGN KEY (company_id) REFERENCES company(id)
Inherits: resource
* Re: Postgres 8.3, four times slower queries?
@ 2009-03-04 23:42 Aaron Guyon <battlemage@gmail.com>
parent: Aaron Guyon <battlemage@gmail.com>
2 siblings, 1 reply; 16+ messages in thread
From: Aaron Guyon @ 2009-03-04 23:42 UTC (permalink / raw)
To: pgsql-performance
2nd part of table descriptions
Attachments:
note2.txt (text/plain, 8.6K) download
Table "public.company"
Column | Type | Modifiers
----------------------+-----------------------------+------------------------
id | integer | not null
name | character varying(255) |
active | integer | not null default 1
status | numeric | not null default 0
last_status_change | date |
not_modified_since | timestamp without time zone |
organization_id | integer |
added_user_id | numeric |
added_tm | timestamp without time zone |
edited_user_id | numeric |
edited_tm | timestamp without time zone |
folder_id | integer |
company_type_id | integer | not null
timezone | character varying(255) |
host_bill_count | numeric |
enforce_departments | boolean | not null default false
zipcode | character varying(255) |
address | character varying(255) |
Indexes:
"company_pkey" PRIMARY KEY, btree (id)
"idx_company_organization_id" btree (organization_id)
"idx_company_lower_name" btree (lower(name::text))
"idx_company_nms" btree (not_modified_since)
Foreign-key constraints:
"company_folder_id_fkey" FOREIGN KEY (folder_id) REFERENCES folder(id)
"company_company_type_id_fkey" FOREIGN KEY (company_type_id) REFERENCES company_type(id)
Inherits: resource
Table "public.project_invoice"
Column | Type | Modifiers
--------------------+-----------------------------+--------------------
id | integer | not null
name | character varying(255) |
active | integer | not null default 1
status | numeric | not null default 0
last_status_change | date |
not_modified_since | timestamp without time zone |
organization_id | integer |
added_user_id | numeric |
added_tm | timestamp without time zone |
edited_user_id | numeric |
edited_tm | timestamp without time zone |
folder_id | integer |
start_date | date |
end_date | date |
owner_resource_id | numeric |
duration | numeric |
saturation | numeric |
Indexes:
"project_invoice_pkey" PRIMARY KEY, btree (id)
"idx_project_invoice_organization_id" btree (organization_id)
"idx_project_invoice_lower_name" btree (lower(name::text))
"idx_project_invoice_nms" btree (not_modified_since)
"idx_project_invoice_owner_resource_id" btree (owner_resource_id)
Inherits: resource
Table "public.resource"
Column | Type | Modifiers
--------------------+-----------------------------+--------------------
id | integer | not null
name | character varying(255) |
active | integer | not null default 1
status | numeric | not null default 0
last_status_change | date |
not_modified_since | timestamp without time zone |
organization_id | integer |
added_user_id | numeric |
added_tm | timestamp without time zone |
edited_user_id | numeric |
edited_tm | timestamp without time zone |
folder_id | integer |
Indexes:
"resource_pkey" PRIMARY KEY, btree (id)
"idx_organization_id" btree (organization_id)
"idx_resource_lower_name" btree (lower(name::text))
Foreign-key constraints:
"resource_folder_id_fkey" FOREIGN KEY (folder_id) REFERENCES folder(id)
Table "public.invoice"
Column | Type | Modifiers
--------------------+-----------------------------+--------------------
id | integer | not null
name | character varying(255) |
active | integer | not null default 1
status | numeric | not null default 0
last_status_change | date |
not_modified_since | timestamp without time zone |
organization_id | integer |
added_user_id | numeric |
added_tm | timestamp without time zone |
edited_user_id | numeric |
edited_tm | timestamp without time zone |
folder_id | integer |
start_date | date | not null
end_date | date |
day_of_week_mask | numeric | not null default 0
start_time | time without time zone | not null
end_time | time without time zone | not null
reservation_id | integer |
weight | numeric |
owner_resource_id | integer |
invoice_group | smallint | not null
Indexes:
"invoice_pkey" PRIMARY KEY, btree (id)
"idx_invoice_organization_id" btree (organization_id)
"idx_invoice_lower_name" btree (lower(name::text))
"idx_invoice_nms" btree (not_modified_since)
"idx_invoice_owner_resource_id" btree (owner_resource_id)
"idx_invoice_reservation_id" btree (reservation_id)
"idx_invoice_start_end_date" btree (start_date, end_date)
Foreign-key constraints:
"invoice_reservation_id_fkey" FOREIGN KEY (reservation_id) REFERENCES reservation(id)
Inherits: resource
Table "public.project"
Column | Type | Modifiers
--------------------+-----------------------------+--------------------
id | integer | not null
name | character varying(255) |
active | integer | not null default 1
status | numeric | not null default 0
last_status_change | date |
not_modified_since | timestamp without time zone |
organization_id | integer |
added_user_id | numeric |
added_tm | timestamp without time zone |
edited_user_id | numeric |
edited_tm | timestamp without time zone |
folder_id | integer |
bank_id | integer | not null
department_id | integer |
x | numeric |
y | numeric |
z | numeric |
width | numeric |
height | numeric |
bill_no | numeric |
calculation_type | numeric | not null
Indexes:
"project_pkey" PRIMARY KEY, btree (id)
"idx_project_department_id" btree (department_id)
"idx_project_organization_id" btree (organization_id)
"idx_project_bank" btree (bank_id)
"idx_project_lower_name" btree (lower(name::text))
"idx_project_nms" btree (not_modified_since)
Foreign-key constraints:
"project_department_id_fkey" FOREIGN KEY (department_id) REFERENCES department(id)
"project_bank_id_fkey" FOREIGN KEY (bank_id) REFERENCES bank(id)
Inherits: resource
Table "public.project_type"
Column | Type | Modifiers
--------------------+-----------------------------+-----------
id | numeric | not null
project_id | integer | not null
active | integer | not null
not_modified_since | timestamp without time zone |
slot_id | integer | not null
day_mask | integer[] |
Indexes:
"resource_project_invoice_pkey" PRIMARY KEY, btree (id)
"idx_project_type_project_id" btree (project_id)
"idx_project_type_slot_id" btree (slot_id)
* Re: Postgres 8.3, four times slower queries?
@ 2009-03-05 13:21 Robert Haas <robertmhaas@gmail.com>
parent: Aaron Guyon <battlemage@gmail.com>
2 siblings, 1 reply; 16+ messages in thread
From: Robert Haas @ 2009-03-05 13:21 UTC (permalink / raw)
To: Aaron Guyon <battlemage@gmail.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Postgres 8.3, four times slower queries?
@ 2009-03-05 16:06 Aaron Guyon <battlemage@gmail.com>
parent: Aaron Guyon <battlemage@gmail.com>
0 siblings, 1 reply; 16+ messages in thread
From: Aaron Guyon @ 2009-03-05 16:06 UTC (permalink / raw)
To: pgsql-performance
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
Attachments:
query_plans.txt (text/plain, 12.9K) download
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=6986.01..6986.02 rows=1 width=20) (actual time=611.061..611.061 rows=0 loops=1)
-> Sort (cost=6986.01..6986.02 rows=1 width=20) (actual time=611.053..611.053 rows=0 loops=1)
Sort Key: t8.id
-> Nested Loop (cost=955.90..6986.00 rows=1 width=20) (actual time=610.942..610.942 rows=0 loops=1)
Join Filter: ((t2.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)) OR (t3.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)) OR (t6.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)) OR (t7.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)) OR (t8.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)) OR (t9.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)) OR (t10.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)) OR (t11.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)))
-> Nested Loop (cost=955.90..4989.27 rows=523 width=76) (actual time=28.274..548.664 rows=1104 loops=1)
-> Hash Join (cost=955.90..3195.56 rows=526 width=52) (actual time=28.123..520.620 rows=1104 loops=1)
Hash Cond: (t6.invoice_id = t7.id)
-> Seq Scan on payment t6 (cost=0.00..2002.69 rows=61789 width=16) (actual time=0.026..253.360 rows=60920 loops=1)
Filter: (active <> 0)
-> Hash (cost=954.15..954.15 rows=140 width=44) (actual time=25.795..25.795 rows=332 loops=1)
-> Nested Loop (cost=0.00..954.15 rows=140 width=44) (actual time=0.546..24.364 rows=332 loops=1)
-> Nested Loop (cost=0.00..562.62 rows=100 width=40) (actual time=0.471..15.759 rows=336 loops=1)
-> Nested Loop (cost=0.00..74.40 rows=156 width=28) (actual time=0.233..6.404 rows=376 loops=1)
-> Nested Loop (cost=0.00..45.04 rows=4 width=20) (actual time=0.157..0.324 rows=4 loops=1)
-> Index Scan using idx_department_du on department t10 (cost=0.00..7.30 rows=6 width=12) (actual time=0.076..0.108 rows=7 loops=1)
Index Cond: (company_id = 250893)
Filter: (active <> 0)
-> Index Scan using idx_project_department_id on project t2 (cost=0.00..6.28 rows=1 width=16) (actual time=0.016..0.018 rows=1 loops=7)
Index Cond: (t2.department_id = t10.id)
Filter: (active <> 0)
-> Index Scan using idx_project_type_project_id on project_type t11 (cost=0.00..6.09 rows=100 width=16) (actual time=0.030..0.817 rows=94 loops=4)
Index Cond: (t2.id = t11.project_id)
Filter: (active <> 0)
-> Index Scan using project_invoice_pkey on project_invoice t3 (cost=0.00..3.12 rows=1 width=12) (actual time=0.007..0.011 rows=1 loops=376)
Index Cond: (t11.slot_id = t3.id)
Filter: (active <> 0)
-> Index Scan using idx_invoice_owner_resource_id on invoice t7 (cost=0.00..3.90 rows=1 width=16) (actual time=0.007..0.011 rows=1 loops=336)
Index Cond: (t3.id = t7.owner_resource_id)
Filter: (active <> 0)
-> Index Scan using idx_payment_amount_payment_id on payment_amount t8 (cost=0.00..3.40 rows=1 width=28) (actual time=0.007..0.011 rows=1 loops=1104)
Index Cond: (t6.id = t8.payment_id)
-> Index Scan using amount_pkey on amount t9 (cost=0.00..3.77 rows=1 width=12) (actual time=0.007..0.011 rows=1 loops=1104)
Index Cond: (t8.amount_id = t9.id)
Total runtime: 611.680 ms
(35 rows)
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=6891.89..6891.90 rows=1 width=20) (actual time=1194.811..1194.811 rows=0 loops=1)
-> Sort (cost=6891.89..6891.89 rows=1 width=20) (actual time=1194.803..1194.803 rows=0 loops=1)
Sort Key: t8.id
Sort Method: quicksort Memory: 17kB
-> Nested Loop (cost=3108.63..6891.88 rows=1 width=20) (actual time=1194.733..1194.733 rows=0 loops=1)
Join Filter: ((t2.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)) OR (t3.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)) OR (t6.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)) OR (t7.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)) OR (t8.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)) OR (t9.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)) OR (t10.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)) OR (t11.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)))
-> Hash Join (cost=3108.63..4816.57 rows=571 width=76) (actual time=799.114..1135.524 rows=1104 loops=1)
Hash Cond: (t8.payment_id = t6.id)
-> Seq Scan on payment_amount t8 (cost=0.00..1441.17 rows=69617 width=28) (actual time=0.042..263.815 rows=69617 loops=1)
-> Hash (cost=3101.44..3101.44 rows=575 width=52) (actual time=593.227..593.227 rows=1104 loops=1)
-> Hash Join (cost=975.62..3101.44 rows=575 width=52) (actual time=98.739..588.389 rows=1104 loops=1)
Hash Cond: (t6.invoice_id = t7.id)
-> Seq Scan on payment t6 (cost=0.00..1891.69 rows=60901 width=16) (actual time=0.035..249.993 rows=60920 loops=1)
Filter: (active <> 0)
-> Hash (cost=973.66..973.66 rows=157 width=44) (actual time=96.404..96.404 rows=332 loops=1)
-> Nested Loop (cost=84.27..973.66 rows=157 width=44) (actual time=23.020..94.924 rows=332 loops=1)
-> Hash Join (cost=84.27..539.32 rows=120 width=40) (actual time=22.881..86.225 rows=336 loops=1)
Hash Cond: (t3.id = t11.slot_id)
-> Seq Scan on project_invoice t3 (cost=0.00..362.56 rows=9129 width=12) (actual time=0.052..38.501 rows=8951 loops=1)
Filter: (active <> 0)
-> Hash (cost=81.97..81.97 rows=184 width=28) (actual time=13.379..13.379 rows=376 loops=1)
-> Nested Loop (cost=0.00..81.97 rows=184 width=28) (actual time=3.188..11.716 rows=376 loops=1)
-> Nested Loop (cost=0.00..51.98 rows=4 width=20) (actual time=0.309..0.499 rows=4 loops=1)
-> Index Scan using idx_department_du on department t10 (cost=0.00..7.96 rows=7 width=12) (actual time=0.181..0.215 rows=7 loops=1)
Index Cond: (company_id = 250893)
Filter: (active <> 0)
-> Index Scan using idx_project_department_id on project t2 (cost=0.00..6.28 rows=1 width=16) (actual time=0.024..0.027 rows=1 loops=7)
Index Cond: (t2.department_id = t10.id)
Filter: (t2.active <> 0)
-> Index Scan using idx_project_type_project_id on project_type t11 (cost=0.00..6.16 rows=107 width=16) (actual time=0.729..2.100 rows=94 loops=4)
Index Cond: (t11.project_id = t2.id)
Filter: (t11.active <> 0)
-> Index Scan using idx_invoice_owner_resource_id on invoice t7 (cost=0.00..3.61 rows=1 width=16) (actual time=0.007..0.011 rows=1 loops=336)
Index Cond: (t7.owner_resource_id = t3.id)
Filter: (t7.active <> 0)
-> Index Scan using amount_pkey on amount t9 (cost=0.00..3.58 rows=1 width=12) (actual time=0.007..0.011 rows=1 loops=1104)
Index Cond: (t9.id = t8.amount_id)
Total runtime: 1195.615 ms
(38 rows)
* Re: Postgres 8.3, four times slower queries?
@ 2009-03-05 16:56 Robert Haas <robertmhaas@gmail.com>
parent: Robert Haas <robertmhaas@gmail.com>
0 siblings, 0 replies; 16+ messages in thread
From: Robert Haas @ 2009-03-05 16:56 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: Aaron Guyon <battlemage@gmail.com>; pgsql-performance
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Postgres 8.3, four times slower queries?
@ 2009-03-05 18:57 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Aaron Guyon <battlemage@gmail.com>
0 siblings, 1 reply; 16+ messages in thread
From: Kevin Grittner @ 2009-03-05 18:57 UTC (permalink / raw)
To: Aaron Guyon <battlemage@gmail.com>; pgsql-performance
>>> 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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Postgres 8.3, four times slower queries?
@ 2009-03-05 19:15 Robert Haas <robertmhaas@gmail.com>
parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 0 replies; 16+ messages in thread
From: Robert Haas @ 2009-03-05 19:15 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: Aaron Guyon <battlemage@gmail.com>; pgsql-performance
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Postgres 8.3, four times slower queries?
@ 2009-03-05 19:58 Aaron Guyon <battlemage@gmail.com>
parent: Aaron Guyon <battlemage@gmail.com>
3 siblings, 0 replies; 16+ messages in thread
From: Aaron Guyon @ 2009-03-05 19:58 UTC (permalink / raw)
To: pgsql-performance
On Thu, Mar 5, 2009 at 12:58 PM, Joshua D. Drake <jd@commandprompt.com>wrote:
> What happens if you do this:
>
> SET cpu_tuple_cost TO '0.5';
> SET cpu_index_tuple_cost TO '0.5';
> EXPLAIN ANALYZE 8.3 query....
>
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.
Attachments:
new_tuple1.txt (text/plain, 6.1K) download
SET
SET
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=17397.17..17397.18 rows=2 width=20) (actual time=621.865..621.865 rows=0 loops=1)
-> Sort (cost=17397.17..17397.18 rows=2 width=20) (actual time=621.857..621.857 rows=0 loops=1)
Sort Key: t8.id
Sort Method: quicksort Memory: 17kB
-> Nested Loop (cost=0.00..17397.16 rows=2 width=20) (actual time=621.825..621.825 rows=0 loops=1)
Join Filter: ((t2.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)) OR (t3.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)) OR (t6.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)) OR (t7.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)) OR (t8.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)) OR (t9.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)) OR (t10.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)) OR (t11.not_modified_since >= to_timestamp('2009-02-24T11:19:57'::text, 'YYYY/MM/DD HH:MI:SS'::text)))
-> Nested Loop (cost=0.00..14615.82 rows=537 width=76) (actual time=51.092..556.405 rows=1104 loops=1)
-> Nested Loop (cost=0.00..12007.84 rows=541 width=52) (actual time=46.530..523.869 rows=1104 loops=1)
-> Nested Loop (cost=0.00..2229.06 rows=147 width=44) (actual time=42.642..501.168 rows=332 loops=1)
-> Nested Loop (cost=0.00..1530.56 rows=114 width=40) (actual time=39.485..489.224 rows=336 loops=1)
-> Nested Loop (cost=0.00..757.31 rows=177 width=28) (actual time=10.877..473.774 rows=376 loops=1)
-> Nested Loop (cost=0.00..69.07 rows=4 width=20) (actual time=6.463..6.640 rows=4 loops=1)
-> Index Scan using idx_department_du on department t10 (cost=0.00..14.72 rows=7 width=12) (actual time=6.141..6.173 rows=7 loops=1)
Index Cond: (company_id = 250893)
Filter: (active <> 0)
-> Index Scan using idx_project_department_id on project t2 (cost=0.00..7.26 rows=1 width=16) (actual time=0.051..0.053 rows=1 loops=7)
Index Cond: (t2.department_id = t10.id)
Filter: (t2.active <> 0)
-> Index Scan using idx_project_type_project_id on project_type t11 (cost=0.00..133.37 rows=77 width=16) (actual time=1.112..116.094 rows=94 loops=4)
Index Cond: (t11.project_id = t2.id)
Filter: (t11.active <> 0)
-> Index Scan using project_invoice_pkey on project_invoice t3 (cost=0.00..3.87 rows=1 width=12) (actual time=0.024..0.027 rows=1 loops=376)
Index Cond: (t3.id = t11.slot_id)
Filter: (t3.active <> 0)
-> Index Scan using idx_invoice_owner_resource_id on invoice t7 (cost=0.00..5.62 rows=1 width=16) (actual time=0.017..0.021 rows=1 loops=336)
Index Cond: (t7.owner_resource_id = t3.id)
Filter: (t7.active <> 0)
-> Index Scan using idx_payment_invoice_id on payment t6 (cost=0.00..52.45 rows=28 width=16) (actual time=0.021..0.037 rows=3 loops=332)
Index Cond: (t6.invoice_id = t7.id)
Filter: (t6.active <> 0)
-> Index Scan using idx_payment_amount_payment_id on payment_amount t8 (cost=0.00..4.32 rows=1 width=28) (actual time=0.011..0.015 rows=1 loops=1104)
Index Cond: (t8.payment_id = t6.id)
-> Index Scan using amount_pkey on amount t9 (cost=0.00..4.64 rows=1 width=12) (actual time=0.012..0.016 rows=1 loops=1104)
Index Cond: (t9.id = t8.amount_id)
Total runtime: 622.377 ms
(35 rows)
* Re: Postgres 8.3, four times slower queries?
@ 2009-03-06 15:50 Aaron Guyon <battlemage@gmail.com>
parent: Aaron Guyon <battlemage@gmail.com>
3 siblings, 0 replies; 16+ messages in thread
From: Aaron Guyon @ 2009-03-06 15:50 UTC (permalink / raw)
To: pgsql-performance
On Thu, Mar 5, 2009 at 12:58 PM, Joshua D. Drake <jd@commandprompt.com>wrote:
> What happens if you do this:
>
> SET cpu_tuple_cost TO '0.5';
> SET cpu_index_tuple_cost TO '0.5';
> EXPLAIN ANALYZE 8.3 query....
>
> Next try this:
>
> SET cpu_tuple_cost TO '0.5';
> SET cpu_index_tuple_cost TO '0.5';
> SET seq_page_cost TO '4.0';
> SET random_page_cost TO '1.0';
> EXPLAIN ANALYZE 8.3 query....
>
> And then this:
>
> SET cpu_tuple_cost TO '0.5';
> SET cpu_index_tuple_cost TO '0.5';
> SET seq_page_cost TO '4.0';
> SET random_page_cost TO '1.0';
> SET effective_cache_size TO '3000MB';
> EXPLAIN ANALYZE 8.3 query....
>
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.
^ permalink raw reply [nested|flat] 16+ messages in thread
end of thread, other threads:[~2009-03-06 15:50 UTC | newest]
Thread overview: 16+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-03-03 00:18 Postgres 8.3, four times slower queries? Aaron Guyon <battlemage@gmail.com>
2009-03-03 03:23 ` Tom Lane <tgl@sss.pgh.pa.us>
2009-03-03 17:31 ` Aaron Guyon <battlemage@gmail.com>
2009-03-03 17:38 ` david@lang.hm
2009-03-03 17:52 ` Aaron Guyon <battlemage@gmail.com>
2009-03-03 22:34 ` Tom Lane <tgl@sss.pgh.pa.us>
2009-03-04 23:20 ` Aaron Guyon <battlemage@gmail.com>
2009-03-04 23:42 ` Aaron Guyon <battlemage@gmail.com>
2009-03-04 23:42 ` Aaron Guyon <battlemage@gmail.com>
2009-03-05 16:06 ` Aaron Guyon <battlemage@gmail.com>
2009-03-05 18:57 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2009-03-05 19:15 ` Robert Haas <robertmhaas@gmail.com>
2009-03-05 13:21 ` Robert Haas <robertmhaas@gmail.com>
2009-03-05 16:56 ` Robert Haas <robertmhaas@gmail.com>
2009-03-05 19:58 ` Aaron Guyon <battlemage@gmail.com>
2009-03-06 15:50 ` Aaron Guyon <battlemage@gmail.com>
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