pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Odd problem with performance in duplicate database
14+ messages / 4 participants
[nested] [flat]

* Odd problem with performance in duplicate database
@ 2003-08-11 21:15 Josh Berkus <josh@agliodbs.com>
  2003-08-11 22:03 ` Re: Odd problem with performance in duplicate database Josh Berkus <josh@agliodbs.com>
  0 siblings, 1 reply; 14+ messages in thread

From: Josh Berkus @ 2003-08-11 21:15 UTC (permalink / raw)
  To: pgsql-performance

Folks:

I have a live and a test database for an in-production system running on 
7.2.4.

The test database is a copy of the live one.   They are running on the same 
copy of Postgres, on the same server.  I use the test database to test 
changes before I apply them to the production system.  Periodically (like 
today) I reload the test system from the backup of the live one.

I'm now having a problem with a huge complex query which runs very slowly on 
the test database, but very quickly on the live database!   The problems 
seems to be that the test database seems to think that a nested loop is an 
appropriate strategy for a table of 140,000 records, while the live one 
realizes that it's not.

What really has me scratching my head is that the test database is an exact 
copy of the live database, just a few hours older.  And the live database has 
never displayed this performance problem, whereas the test database has had 
it for 2-3 weeks.

Both EXPLAIN ANALYZEs are attached.

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco

Attachments:

perf_prob_live.txt (text/plain, 11.1K) download Sort (cost=57376.11..57376.11 rows=1 width=627) (actual time=370.48..370.48 rows=8 loops=1) -> Nested Loop (cost=57049.91..57376.10 rows=1 width=627) (actual time=360.99..369.81 rows=8 loops=1) -> Nested Loop (cost=57049.91..57372.18 rows=1 width=583) (actual time=314.97..320.96 rows=8 loops=1) -> Nested Loop (cost=57049.91..57368.25 rows=1 width=539) (actual time=314.90..320.56 rows=8 loops=1) -> Nested Loop (cost=57049.91..57365.27 rows=1 width=525) (actual time=314.42..317.60 rows=8 loops=1) -> Nested Loop (cost=57049.91..57361.78 rows=1 width=497) (actual time=314.35..317.10 rows=8 loops=1) -> Nested Loop (cost=57049.91..57357.83 rows=1 width=436) (actual time=314.27..316.53 rows=8 loops=1) -> Merge Join (cost=57049.91..57050.46 rows=77 width=424) (actual time=314.02..314.77 rows=26 loops=1) -> Sort (cost=57048.31..57048.31 rows=37 width=406) (actual time=313.76..313.78 rows=27 loops=1) -> Merge Join (cost=57019.29..57047.82 rows=37 width=406) (actual time=311.61..313.63 rows=27 loops=1) -> Index Scan using locations_pkey on locations (cost=0.00..27.54 rows=568 width=107) (actual time=0.22..1.55 rows=172 loops=1) -> Sort (cost=57019.29..57019.29 rows=37 width=299) (actual time=311.34..311.35 rows=27 loops=1) -> Hash Join (cost=5.73..57018.80 rows=37 width=299) (actual time=44.24..311.12 rows=27 loops=1) -> Hash Join (cost=4.61..57015.58 rows=745 width=274) (actual time=3.30..308.58 rows=965 loops=1) -> Index Scan using idx_events_date on events (cost=0.00..57004.46 rows=745 width=240) (actual time=2.54..296.46 rows=965 loops=1) SubPlan -> Nested Loop (cost=5.48..19.48 rows=1 width=55) (actual time=0.05..0.05 rows=0 loops=856) -> Nested Loop (cost=0.00..12.52 rows=1 width=23) (actual time=0.03..0.04 rows=0 loops=856) -> Nested Loop (cost=0.00..7.41 rows=1 width=12) (actual time=0.01..0.02 rows=1 loops=856) -> Index Scan using trial_groups_pkey on trial_groups (cost=0.00..3.49 rows=1 width=4) (actual time=0.01..0.01 rows=0 loops=856) -> Index Scan using idx_cases_tgroup on cases (cost=0.00..3.92 rows=1 width=8) (actual time=0.02..0.04 rows=4 loops=133) -> Index Scan using idx_caseclients_case on case_clients (cost=0.00..5.10 rows=1 width=11) (actual time=0.03..0.04 rows=1 loops=471) -> Materialize (cost=6.95..6.95 rows=1 width=32) (actual time=0.00..0.00 rows=3 loops=366) -> Nested Loop (cost=5.48..6.95 rows=1 width=32) (actual time=0.58..0.81 rows=3 loops=1) -> Merge Join (cost=5.48..5.50 rows=1 width=30) (actual time=0.55..0.56 rows=3 loops=1) -> Sort (cost=4.04..4.04 rows=2 width=28) (actual time=0.39..0.39 rows=3 loops=1) -> Merge Join (cost=3.99..4.03 rows=2 width=28) (actual time=0.31..0.32 rows=3 loops=1) -> Sort (cost=1.34..1.34 rows=21 width=8) (actual time=0.12..0.12 rows=2 loops=1) -> Seq Scan on text_lists (cost=0.00..1.10 rows=21 width=8) (actual time=0.01..0.06 rows=21 loops=1) -> Sort (cost=2.65..2.65 rows=2 width=20) (actual time=0.18..0.18 rows=3 loops=1) -> Seq Scan on list_values (cost=0.00..2.65 rows=2 width=20) (actual time=0.01..0.15 rows=3 loops=1) -> Sort (cost=1.45..1.45 rows=3 width=2) (actual time=0.13..0.13 rows=5 loops=1) -> Seq Scan on status s1 (cost=0.00..1.44 rows=3 width=2) (actual time=0.01..0.09 rows=4 loops=1) -> Seq Scan on status s2 (cost=0.00..1.44 rows=3 width=2) (actual time=0.01..0.07 rows=3 loops=3) -> Nested Loop (cost=5.48..16.00 rows=1 width=47) (actual time=0.06..0.06 rows=0 loops=3325) -> Nested Loop (cost=0.00..9.05 rows=1 width=15) (actual time=0.05..0.06 rows=1 loops=3325) -> Index Scan using cases_pkey on cases (cost=0.00..3.94 rows=1 width=4) (actual time=0.02..0.02 rows=1 loops=3325) -> Index Scan using idx_caseclients_case on case_clients (cost=0.00..5.10 rows=1 width=11) (actual time=0.03..0.03 rows=1 loops=3311) -> Materialize (cost=6.95..6.95 rows=1 width=32) (actual time=0.00..0.00 rows=2 loops=2190) -> Nested Loop (cost=5.48..6.95 rows=1 width=32) (actual time=0.63..0.85 rows=3 loops=1) -> Merge Join (cost=5.48..5.50 rows=1 width=30) (actual time=0.59..0.60 rows=3 loops=1) -> Sort (cost=4.04..4.04 rows=2 width=28) (actual time=0.43..0.43 rows=3 loops=1) -> Merge Join (cost=3.99..4.03 rows=2 width=28) (actual time=0.35..0.36 rows=3 loops=1) -> Sort (cost=1.34..1.34 rows=21 width=8) (actual time=0.14..0.14 rows=2 loops=1) -> Seq Scan on text_lists (cost=0.00..1.10 rows=21 width=8) (actual time=0.02..0.06 rows=21 loops=1) -> Sort (cost=2.65..2.65 rows=2 width=20) (actual time=0.19..0.20 rows=3 loops=1) -> Seq Scan on list_values (cost=0.00..2.65 rows=2 width=20) (actual time=0.02..0.17 rows=3 loops=1) -> Sort (cost=1.45..1.45 rows=3 width=2) (actual time=0.14..0.14 rows=5 loops=1) -> Seq Scan on status s1 (cost=0.00..1.44 rows=3 width=2) (actual time=0.02..0.10 rows=4 loops=1) -> Seq Scan on status s2 (cost=0.00..1.44 rows=3 width=2) (actual time=0.01..0.07 rows=3 loops=3) -> Hash (cost=4.49..4.49 rows=97 width=34) (actual time=0.52..0.52 rows=0 loops=1) -> Seq Scan on event_types (cost=0.00..4.49 rows=97 width=34) (actual time=0.06..0.38 rows=97 loops=1) -> Hash (cost=1.12..1.12 rows=1 width=25) (actual time=0.09..0.09 rows=0 loops=1) -> Seq Scan on event_cats (cost=0.00..1.12 rows=1 width=25) (actual time=0.07..0.08 rows=1 loops=1) -> Sort (cost=1.61..1.61 rows=10 width=18) (actual time=0.21..0.23 rows=30 loops=1) -> Seq Scan on status (cost=0.00..1.52 rows=10 width=18) (actual time=0.04..0.12 rows=9 loops=1) -> Index Scan using event_days_pk on event_days (cost=0.00..3.96 rows=1 width=12) (actual time=0.05..0.05 rows=0 loops=26) -> Index Scan using cases_pkey on cases (cost=0.00..3.94 rows=1 width=61) (actual time=0.02..0.03 rows=0 loops=8) -> Index Scan using trial_groups_pkey on trial_groups (cost=0.00..3.49 rows=1 width=28) (actual time=0.01..0.02 rows=0 loops=8) -> Seq Scan on opp_counsel (cost=0.00..2.44 rows=87 width=14) (actual time=0.01..0.18 rows=87 loops=8) -> Index Scan using events_pkey on events parent (cost=0.00..3.92 rows=1 width=44) (actual time=0.00..0.00 rows=0 loops=8) -> Index Scan using events_pkey on events previous (cost=0.00..3.92 rows=1 width=44) (actual time=0.02..0.02 rows=0 loops=8) SubPlan -> Aggregate (cost=9.01..9.01 rows=1 width=23) (actual time=0.35..0.35 rows=1 loops=4) -> Index Scan using idx_cases_tgroup on cases c1 (cost=0.00..9.01 rows=1 width=23) (actual time=0.08..0.23 rows=4 loops=4) SubPlan -> Index Scan using idx_caseclients_case on case_clients cc1 (cost=0.00..5.09 rows=1 width=7) (actual time=0.03..0.03 rows=1 loops=22) Total runtime: 373.70 msec
perf_prob_test.txt (text/plain, 11.4K) download Sort (cost=8725694.92..8725694.92 rows=1 width=625) (actual time=266461.38..266461.38 rows=8 loops=1) -> Nested Loop (cost=8725228.42..8725694.91 rows=1 width=625) (actual time=266452.64..266461.09 rows=8 loops=1) -> Nested Loop (cost=8725228.42..8725691.01 rows=1 width=581) (actual time=266442.99..266448.67 rows=8 loops=1) -> Nested Loop (cost=8725228.42..8725687.12 rows=1 width=537) (actual time=266442.93..266448.29 rows=8 loops=1) -> Nested Loop (cost=8725228.42..8725684.14 rows=1 width=523) (actual time=266442.45..266445.48 rows=8 loops=1) -> Nested Loop (cost=8725228.42..8725680.64 rows=1 width=495) (actual time=266442.38..266444.99 rows=8 loops=1) -> Nested Loop (cost=8725228.42..8725676.67 rows=1 width=434) (actual time=266442.30..266444.43 rows=8 loops=1) -> Merge Join (cost=8725228.42..8725229.20 rows=113 width=422) (actual time=266442.08..266442.77 rows=26 loops=1) -> Sort (cost=8725226.81..8725226.81 rows=54 width=404) (actual time=266441.54..266441.56 rows=27 loops=1) -> Merge Join (cost=8725197.37..8725226.03 rows=54 width=404) (actual time=266439.42..266441.41 rows=27 loops=1) -> Index Scan using locations_pkey on locations (cost=0.00..27.54 rows=568 width=107) (actual time=0.14..1.44 rows=172 loops=1) -> Sort (cost=8725197.37..8725197.37 rows=54 width=297) (actual time=266439.24..266439.25 rows=27 loops=1) -> Hash Join (cost=5.73..8725196.59 rows=54 width=297) (actual time=28490.20..266438.99 rows=27 loops=1) -> Hash Join (cost=4.60..8725192.42 rows=1083 width=272) (actual time=2.25..266436.04 rows=964 loops=1) -> Index Scan using idx_events_date on events (cost=0.00..8725178.35 rows=1083 width=238) (actual time=1.49..266421.59 rows=964 loops=1) SubPlan -> Hash Join (cost=89.59..3792.14 rows=1 width=55) (actual time=311.23..311.23 rows=0 loops=855) -> Hash Join (cost=82.64..3784.86 rows=88 width=23) (actual time=301.99..310.66 rows=0 loops=855) -> Seq Scan on case_clients (cost=0.00..3673.48 rows=11274 width=11) (actual time=0.02..302.20 rows=8822 loops=855) -> Hash (cost=81.53..81.53 rows=887 width=12) (actual time=0.04..0.04 rows=0 loops=855) -> Nested Loop (cost=0.00..81.53 rows=887 width=12) (actual time=0.03..0.04 rows=1 loops=855) -> Index Scan using trial_groups_pkey on trial_groups (cost=0.00..3.49 rows=1 width=4) (actual time=0.02..0.02 rows=0 loops=855) -> Index Scan using idx_cases_tgroup on cases (cost=0.00..77.77 rows=43 width=8) (actual time=0.03..0.07 rows=6 loops=133) -> Hash (cost=6.95..6.95 rows=1 width=32) (actual time=0.30..0.30 rows=0 loops=855) -> Nested Loop (cost=5.48..6.95 rows=1 width=32) (actual time=0.06..0.30 rows=3 loops=855) -> Merge Join (cost=5.48..5.50 rows=1 width=30) (actual time=0.02..0.04 rows=3 loops=855) -> Sort (cost=4.04..4.04 rows=2 width=28) (actual time=0.00..0.00 rows=3 loops=855) -> Merge Join (cost=3.99..4.03 rows=2 width=28) (actual time=0.30..0.30 rows=3 loops=1) -> Sort (cost=1.34..1.34 rows=21 width=8) (actual time=0.11..0.11 rows=2 loops=1) -> Seq Scan on text_lists (cost=0.00..1.10 rows=21 width=8) (actual time=0.01..0.05 rows=21 loops=1) -> Sort (cost=2.65..2.65 rows=2 width=20) (actual time=0.18..0.18 rows=3 loops=1) -> Seq Scan on list_values (cost=0.00..2.65 rows=2 width=20) (actual time=0.01..0.14 rows=3 loops=1) -> Sort (cost=1.45..1.45 rows=3 width=2) (actual time=0.00..0.00 rows=5 loops=855) -> Seq Scan on status s1 (cost=0.00..1.44 rows=3 width=2) (actual time=0.01..0.08 rows=4 loops=1) -> Seq Scan on status s2 (cost=0.00..1.44 rows=3 width=2) (actual time=0.01..0.07 rows=3 loops=2565) -> Nested Loop (cost=5.48..15.71 rows=1 width=47) (actual time=0.07..0.07 rows=0 loops=3312) -> Nested Loop (cost=0.00..8.76 rows=1 width=15) (actual time=0.06..0.07 rows=1 loops=3312) -> Index Scan using cases_pkey on cases (cost=0.00..3.96 rows=1 width=4) (actual time=0.02..0.02 rows=1 loops=3312) -> Index Scan using idx_caseclients_case on case_clients (cost=0.00..4.79 rows=1 width=11) (actual time=0.03..0.03 rows=1 loops=3298) -> Materialize (cost=6.95..6.95 rows=1 width=32) (actual time=0.00..0.00 rows=2 loops=2182) -> Nested Loop (cost=5.48..6.95 rows=1 width=32) (actual time=0.68..0.89 rows=3 loops=1) -> Merge Join (cost=5.48..5.50 rows=1 width=30) (actual time=0.64..0.66 rows=3 loops=1) -> Sort (cost=4.04..4.04 rows=2 width=28) (actual time=0.50..0.50 rows=3 loops=1) -> Merge Join (cost=3.99..4.03 rows=2 width=28) (actual time=0.42..0.43 rows=3 loops=1) -> Sort (cost=1.34..1.34 rows=21 width=8) (actual time=0.16..0.16 rows=2 loops=1) -> Seq Scan on text_lists (cost=0.00..1.10 rows=21 width=8) (actual time=0.06..0.09 rows=21 loops=1) -> Sort (cost=2.65..2.65 rows=2 width=20) (actual time=0.24..0.24 rows=3 loops=1) -> Seq Scan on list_values (cost=0.00..2.65 rows=2 width=20) (actual time=0.06..0.20 rows=3 loops=1) -> Sort (cost=1.45..1.45 rows=3 width=2) (actual time=0.12..0.12 rows=5 loops=1) -> Seq Scan on status s1 (cost=0.00..1.44 rows=3 width=2) (actual time=0.01..0.09 rows=4 loops=1) -> Seq Scan on status s2 (cost=0.00..1.44 rows=3 width=2) (actual time=0.01..0.07 rows=3 loops=3) -> Hash (cost=4.48..4.48 rows=96 width=34) (actual time=0.51..0.51 rows=0 loops=1) -> Seq Scan on event_types (cost=0.00..4.48 rows=96 width=34) (actual time=0.05..0.36 rows=96 loops=1) -> Hash (cost=1.12..1.12 rows=1 width=25) (actual time=0.08..0.08 rows=0 loops=1) -> Seq Scan on event_cats (cost=0.00..1.12 rows=1 width=25) (actual time=0.06..0.07 rows=1 loops=1) -> Sort (cost=1.61..1.61 rows=10 width=18) (actual time=0.49..0.51 rows=30 loops=1) -> Seq Scan on status (cost=0.00..1.52 rows=10 width=18) (actual time=0.05..0.14 rows=9 loops=1) -> Index Scan using event_days_pk on event_days (cost=0.00..3.96 rows=1 width=12) (actual time=0.05..0.05 rows=0 loops=26) -> Index Scan using cases_pkey on cases (cost=0.00..3.96 rows=1 width=61) (actual time=0.03..0.03 rows=0 loops=8) -> Index Scan using trial_groups_pkey on trial_groups (cost=0.00..3.49 rows=1 width=28) (actual time=0.02..0.02 rows=0 loops=8) -> Seq Scan on opp_counsel (cost=0.00..2.44 rows=87 width=14) (actual time=0.01..0.17 rows=87 loops=8) -> Index Scan using events_pkey on events parent (cost=0.00..3.89 rows=1 width=44) (actual time=0.00..0.00 rows=0 loops=8) -> Index Scan using events_pkey on events previous (cost=0.00..3.89 rows=1 width=44) (actual time=0.02..0.02 rows=0 loops=8) SubPlan -> Aggregate (cost=283.08..283.08 rows=1 width=23) (actual time=0.33..0.33 rows=1 loops=4) -> Index Scan using idx_cases_tgroup on cases c1 (cost=0.00..283.05 rows=21 width=23) (actual time=0.08..0.23 rows=4 loops=4) SubPlan -> Index Scan using idx_caseclients_case on case_clients cc1 (cost=0.00..4.78 rows=1 width=7) (actual time=0.03..0.03 rows=1 loops=22) Total runtime: 266463.54 msec
^ permalink raw reply [nested|flat] 14+ messages in thread

* Re: Odd problem with performance in duplicate database
  2003-08-11 21:15 Odd problem with performance in duplicate database Josh Berkus <josh@agliodbs.com>
@ 2003-08-11 22:03 ` Josh Berkus <josh@agliodbs.com>
  2003-08-11 22:42   ` Re: Odd problem with performance in duplicate database Ron Johnson <ron.l.johnson@cox.net>
  0 siblings, 1 reply; 14+ messages in thread

From: Josh Berkus @ 2003-08-11 22:03 UTC (permalink / raw)
  To: pgsql-performance

Folks,

More followup on this:

The crucial difference between the two execution plans is this clause:

test db has:
->  Seq Scan on case_clients  (cost=0.00..3673.48 rows=11274 width=11) (actual 
time=0.02..302.20 rows=8822 loops=855)

whereas live db has:
->  Index Scan using idx_caseclients_case on case_clients  (cost=0.00..5.10 
rows=1 width=11) (actual time=0.03..0.04 rows=1 loops=471)

using an enable_seqscan = false fixes this, but is obviously not a long-term 
solution.   

I've re-created the test system from an immediate copy of the live database, 
and checked that the the main tables and indexes were reproduced faithfully.

Lowering random_page_cost seems to do the trick.  But I'm still mystified; why 
would one identical database pick a different plan than its copy?

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco




^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: Odd problem with performance in duplicate database
  2003-08-11 21:15 Odd problem with performance in duplicate database Josh Berkus <josh@agliodbs.com>
  2003-08-11 22:03 ` Re: Odd problem with performance in duplicate database Josh Berkus <josh@agliodbs.com>
@ 2003-08-11 22:42   ` Ron Johnson <ron.l.johnson@cox.net>
  2003-08-11 22:48     ` Re: Odd problem with performance in duplicate database Josh Berkus <josh@agliodbs.com>
  0 siblings, 1 reply; 14+ messages in thread

From: Ron Johnson @ 2003-08-11 22:42 UTC (permalink / raw)
  To: pgsql-performance

On Mon, 2003-08-11 at 17:03, Josh Berkus wrote:
> Folks,
> 
> More followup on this:
> 
> The crucial difference between the two execution plans is this clause:
> 
> test db has:
> ->  Seq Scan on case_clients  (cost=0.00..3673.48 rows=11274 width=11) (actual 
> time=0.02..302.20 rows=8822 loops=855)
> 
> whereas live db has:
> ->  Index Scan using idx_caseclients_case on case_clients  (cost=0.00..5.10 
> rows=1 width=11) (actual time=0.03..0.04 rows=1 loops=471)
> 
> using an enable_seqscan = false fixes this, but is obviously not a long-term 
> solution.   
> 
> I've re-created the test system from an immediate copy of the live database, 
> and checked that the the main tables and indexes were reproduced faithfully.
> 
> Lowering random_page_cost seems to do the trick.  But I'm still mystified; why 
> would one identical database pick a different plan than its copy?

If the databases are on different machines, maybe the postgres.conf
or pg_hba.conf files are different, and the buffer counts is affect-
ing the optimizer?

-- 
+---------------------------------------------------------------+
| Ron Johnson, Jr.        Home: ron.l.johnson@cox.net           |
| Jefferson, LA  USA                                            |
|                                                               |
| "Man, I'm pretty.  Hoo Hah!"                                  |
|    Johnny Bravo                                               |
+---------------------------------------------------------------+





^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: Odd problem with performance in duplicate database
  2003-08-11 21:15 Odd problem with performance in duplicate database Josh Berkus <josh@agliodbs.com>
  2003-08-11 22:03 ` Re: Odd problem with performance in duplicate database Josh Berkus <josh@agliodbs.com>
  2003-08-11 22:42   ` Re: Odd problem with performance in duplicate database Ron Johnson <ron.l.johnson@cox.net>
@ 2003-08-11 22:48     ` Josh Berkus <josh@agliodbs.com>
  2003-08-11 22:51       ` Re: Odd problem with performance in duplicate database Peter Darley <pdarley@kinesis-cem.com>
  0 siblings, 1 reply; 14+ messages in thread

From: Josh Berkus @ 2003-08-11 22:48 UTC (permalink / raw)
  To: Ron Johnson <ron.l.johnson@cox.net>; pgsql-performance

Ron,

> If the databases are on different machines, maybe the postgres.conf
> or pg_hba.conf files are different, and the buffer counts is affect-
> ing the optimizer?

The databases are on the same machine, using the same postmaster.

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco




^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: Odd problem with performance in duplicate database
  2003-08-11 21:15 Odd problem with performance in duplicate database Josh Berkus <josh@agliodbs.com>
  2003-08-11 22:03 ` Re: Odd problem with performance in duplicate database Josh Berkus <josh@agliodbs.com>
  2003-08-11 22:42   ` Re: Odd problem with performance in duplicate database Ron Johnson <ron.l.johnson@cox.net>
  2003-08-11 22:48     ` Re: Odd problem with performance in duplicate database Josh Berkus <josh@agliodbs.com>
@ 2003-08-11 22:51       ` Peter Darley <pdarley@kinesis-cem.com>
  2003-08-11 22:59         ` Re: Odd problem with performance in duplicate database Josh Berkus <josh@agliodbs.com>
  2003-08-12 00:29         ` Re: Odd problem with performance in duplicate database Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 2 replies; 14+ messages in thread

From: Peter Darley @ 2003-08-11 22:51 UTC (permalink / raw)
  To: josh@agliodbs.com; Ron Johnson <ron.l.johnson@cox.net>; pgsql-performance

Josh,
	I'm sure that you've thought of this, but it sounds like you may not have
done an analyze in your new DB.
Thanks,
Peter Darley

-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org]On Behalf Of Josh Berkus
Sent: Monday, August 11, 2003 3:48 PM
To: Ron Johnson; PgSQL Performance ML
Subject: Re: [PERFORM] Odd problem with performance in duplicate
database


Ron,

> If the databases are on different machines, maybe the postgres.conf
> or pg_hba.conf files are different, and the buffer counts is affect-
> ing the optimizer?

The databases are on the same machine, using the same postmaster.

--
-Josh Berkus
 Aglio Database Solutions
 San Francisco


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)





^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: Odd problem with performance in duplicate database
  2003-08-11 21:15 Odd problem with performance in duplicate database Josh Berkus <josh@agliodbs.com>
  2003-08-11 22:03 ` Re: Odd problem with performance in duplicate database Josh Berkus <josh@agliodbs.com>
  2003-08-11 22:42   ` Re: Odd problem with performance in duplicate database Ron Johnson <ron.l.johnson@cox.net>
  2003-08-11 22:48     ` Re: Odd problem with performance in duplicate database Josh Berkus <josh@agliodbs.com>
  2003-08-11 22:51       ` Re: Odd problem with performance in duplicate database Peter Darley <pdarley@kinesis-cem.com>
@ 2003-08-11 22:59         ` Josh Berkus <josh@agliodbs.com>
  2003-08-11 23:22           ` Re: Odd problem with performance in duplicate database Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 1 reply; 14+ messages in thread

From: Josh Berkus @ 2003-08-11 22:59 UTC (permalink / raw)
  To: pgsql-performance

Peter,

> 	I'm sure that you've thought of this, but it sounds like you may not have
> done an analyze in your new DB.

Yes.  Also a VACUUM.  Also forcing a REINDEX on the major involved tables.

Also running counts on the pg_* system tables to see if any objects did not 
get restored from the backup as compared with the live database.

By everything I can measure, the live database and the test are identical; yet 
the test does not think that idx_caseclients_case is very accessable, and the 
live database knows it is.

Is this perhaps a bug with ANALYZE statistics in 7.2.4?  I know that in that 
version I don't have the option of increasing the statistics sampling ...

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco




^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: Odd problem with performance in duplicate database
  2003-08-11 21:15 Odd problem with performance in duplicate database Josh Berkus <josh@agliodbs.com>
  2003-08-11 22:03 ` Re: Odd problem with performance in duplicate database Josh Berkus <josh@agliodbs.com>
  2003-08-11 22:42   ` Re: Odd problem with performance in duplicate database Ron Johnson <ron.l.johnson@cox.net>
  2003-08-11 22:48     ` Re: Odd problem with performance in duplicate database Josh Berkus <josh@agliodbs.com>
  2003-08-11 22:51       ` Re: Odd problem with performance in duplicate database Peter Darley <pdarley@kinesis-cem.com>
  2003-08-11 22:59         ` Re: Odd problem with performance in duplicate database Josh Berkus <josh@agliodbs.com>
@ 2003-08-11 23:22           ` Tom Lane <tgl@sss.pgh.pa.us>
  2003-08-11 23:46             ` Re: Odd problem with performance in duplicate database Tom Lane <tgl@sss.pgh.pa.us>
  2003-08-11 23:51             ` Re: Odd problem with performance in duplicate database Josh Berkus <josh@agliodbs.com>
  0 siblings, 2 replies; 14+ messages in thread

From: Tom Lane @ 2003-08-11 23:22 UTC (permalink / raw)
  To: josh@agliodbs.com; +Cc: pgsql-performance

Josh Berkus <josh@agliodbs.com> writes:
> By everything I can measure, the live database and the test are
> identical; yet the test does not think that idx_caseclients_case is
> very accessable, and the live database knows it is.

Let's see the pg_stats rows for case_clients in both databases.  The
entries for trial_groups might be relevant too.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: Odd problem with performance in duplicate database
  2003-08-11 21:15 Odd problem with performance in duplicate database Josh Berkus <josh@agliodbs.com>
  2003-08-11 22:03 ` Re: Odd problem with performance in duplicate database Josh Berkus <josh@agliodbs.com>
  2003-08-11 22:42   ` Re: Odd problem with performance in duplicate database Ron Johnson <ron.l.johnson@cox.net>
  2003-08-11 22:48     ` Re: Odd problem with performance in duplicate database Josh Berkus <josh@agliodbs.com>
  2003-08-11 22:51       ` Re: Odd problem with performance in duplicate database Peter Darley <pdarley@kinesis-cem.com>
  2003-08-11 22:59         ` Re: Odd problem with performance in duplicate database Josh Berkus <josh@agliodbs.com>
  2003-08-11 23:22           ` Re: Odd problem with performance in duplicate database Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-08-11 23:46             ` Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 14+ messages in thread

From: Tom Lane @ 2003-08-11 23:46 UTC (permalink / raw)
  To: josh@agliodbs.com; +Cc: pgsql-performance

Josh Berkus <josh@agliodbs.com> writes:
> Still, they are differences.  Attached.

Actually, it was mainly "cases" that I wanted to know about ---
specifically, whichever columns are in "idx_cases_tgroup".
Also, which of the trial_groups columns is the pkey?

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: Odd problem with performance in duplicate database
  2003-08-11 21:15 Odd problem with performance in duplicate database Josh Berkus <josh@agliodbs.com>
  2003-08-11 22:03 ` Re: Odd problem with performance in duplicate database Josh Berkus <josh@agliodbs.com>
  2003-08-11 22:42   ` Re: Odd problem with performance in duplicate database Ron Johnson <ron.l.johnson@cox.net>
  2003-08-11 22:48     ` Re: Odd problem with performance in duplicate database Josh Berkus <josh@agliodbs.com>
  2003-08-11 22:51       ` Re: Odd problem with performance in duplicate database Peter Darley <pdarley@kinesis-cem.com>
  2003-08-11 22:59         ` Re: Odd problem with performance in duplicate database Josh Berkus <josh@agliodbs.com>
  2003-08-11 23:22           ` Re: Odd problem with performance in duplicate database Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-08-11 23:51             ` Josh Berkus <josh@agliodbs.com>
  2003-08-11 23:59               ` Re: Odd problem with performance in duplicate database Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 1 reply; 14+ messages in thread

From: Josh Berkus @ 2003-08-11 23:51 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance

Tom,

> Let's see the pg_stats rows for case_clients in both databases.  The
> entries for trial_groups might be relevant too.

My reading is that the case is "borderline"; that is, becuase the correlation 
is about 10-20% higher on the test database (since it was restored "clean" 
from backup) the planner is resorting to a seq scan.

At which point the spectre of random_page_cost less than 1.0 rears its ugly 
head again.  Because the planner seems to regard this as a borderline case, 
but it's far from borderline ... index scan takes 260ms, seq scan takes 
244,000ms.   Yet my random_page_cost is set pretty low already, at 1.5.

It seems like I'd have to set random_page_cost to less than 1.0 to make sure 
that the planner never used a seq scan.  Which kinda defies the meaning of 
the setting.

*sigh* wish the client would pay for an upgrade ....

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco




^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: Odd problem with performance in duplicate database
  2003-08-11 21:15 Odd problem with performance in duplicate database Josh Berkus <josh@agliodbs.com>
  2003-08-11 22:03 ` Re: Odd problem with performance in duplicate database Josh Berkus <josh@agliodbs.com>
  2003-08-11 22:42   ` Re: Odd problem with performance in duplicate database Ron Johnson <ron.l.johnson@cox.net>
  2003-08-11 22:48     ` Re: Odd problem with performance in duplicate database Josh Berkus <josh@agliodbs.com>
  2003-08-11 22:51       ` Re: Odd problem with performance in duplicate database Peter Darley <pdarley@kinesis-cem.com>
  2003-08-11 22:59         ` Re: Odd problem with performance in duplicate database Josh Berkus <josh@agliodbs.com>
  2003-08-11 23:22           ` Re: Odd problem with performance in duplicate database Tom Lane <tgl@sss.pgh.pa.us>
  2003-08-11 23:51             ` Re: Odd problem with performance in duplicate database Josh Berkus <josh@agliodbs.com>
@ 2003-08-11 23:59               ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 14+ messages in thread

From: Tom Lane @ 2003-08-11 23:59 UTC (permalink / raw)
  To: josh@agliodbs.com; +Cc: pgsql-performance

Josh Berkus <josh@agliodbs.com> writes:
> My reading is that the case is "borderline";

Well, clearly the planner is flipping to a much less desirable plan, but
the core estimation error is not borderline by my standards.  In the
live DB we have this subplan:

->  Nested Loop  (cost=0.00..7.41 rows=1 width=12) (actual time=0.01..0.02 rows=1 loops=856)
    ->  Index Scan using trial_groups_pkey on trial_groups  (cost=0.00..3.49 rows=1 width=4) (actual time=0.01..0.01 rows=0 loops=856)
    ->  Index Scan using idx_cases_tgroup on cases  (cost=0.00..3.92 rows=1 width=8) (actual time=0.02..0.04 rows=4 loops=133)

In the test DB, the identical subplan is estimated at:

->  Nested Loop  (cost=0.00..81.53 rows=887 width=12) (actual time=0.03..0.04 rows=1 loops=855)
    ->  Index Scan using trial_groups_pkey on trial_groups  (cost=0.00..3.49 rows=1 width=4) (actual time=0.02..0.02 rows=0 loops=855)
    ->  Index Scan using idx_cases_tgroup on cases  (cost=0.00..77.77 rows=43 width=8) (actual time=0.03..0.07 rows=6 loops=133)

and that factor of 887 error in the output rows estimate is what's
driving all the outer plan steps to make bad choices.

The "trial_groups_pkey" estimate is the same in both databases,
so it's presumably a problem with estimating the number of
matches to a "trial_groups" row that will be found in "cases".
This is dependent on the pg_stats entries for the relevant
columns, which I'm still hoping to see ...

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: Odd problem with performance in duplicate database
  2003-08-11 21:15 Odd problem with performance in duplicate database Josh Berkus <josh@agliodbs.com>
  2003-08-11 22:03 ` Re: Odd problem with performance in duplicate database Josh Berkus <josh@agliodbs.com>
  2003-08-11 22:42   ` Re: Odd problem with performance in duplicate database Ron Johnson <ron.l.johnson@cox.net>
  2003-08-11 22:48     ` Re: Odd problem with performance in duplicate database Josh Berkus <josh@agliodbs.com>
  2003-08-11 22:51       ` Re: Odd problem with performance in duplicate database Peter Darley <pdarley@kinesis-cem.com>
@ 2003-08-12 00:29         ` Tom Lane <tgl@sss.pgh.pa.us>
  2003-08-12 00:43           ` Re: Odd problem with performance in duplicate database Josh Berkus <josh@agliodbs.com>
  2003-08-12 00:51           ` Re: Odd problem with performance in duplicate database Josh Berkus <josh@agliodbs.com>
  1 sibling, 2 replies; 14+ messages in thread

From: Tom Lane @ 2003-08-12 00:29 UTC (permalink / raw)
  To: josh@agliodbs.com; +Cc: pgsql-performance

Josh Berkus <josh@agliodbs.com> writes:
> Tom,
>> Okay, here's our problem:
>> 
>> live DB: tgroup_id  n_distinct = -1
>> 
>> test DN: tgroup_id  n_distinct = 11
>> 
>> The former estimate actually means that it thinks tgroup_id is a unique
>> column, whereas the latter says there are only 11 distinct values in the
>> column.  I assume the former is much nearer to the truth (how many rows
>> in cases, and how many distinct tgroup_id values)?

> The real case is that there are 113 distinct tgroup_ids, which cover
> about 10% of the population of cases.  The other 90% is NULL.  The
> average tgroup_id is shared between 4.7 cases.

> So this seems like sampling error.

Partly.  The numbers suggest that in ANALYZE's default sample of 3000
rows, it's only finding about a dozen non-null tgroup_ids (yielding the
0.996 null_frac value); and that in one case all dozen are different and
in the other case there are two duplicates.  It would help if you
boosted the stats target for this column by a factor of 10.  (You can
do that in 7.2, btw --- IIRC the only problem is that a pg_dump won't
show that you did so.)

But the other part of the problem is that in 7.2, the join selectivity
estimator is way off when you are joining a unique column (like the pkey
on the other side) to a column with a very large fraction of nulls.
We only discovered this recently; it's fixed as of 7.3.3:

2003-04-15 01:18  tgl

	* src/backend/utils/adt/selfuncs.c (REL7_3_STABLE): eqjoinsel's
	logic for case where MCV lists are not present should account for
	NULLs; in hindsight this is obvious since the code for the
	MCV-lists case would reduce to this when there are zero entries in
	both lists.  Per example from Alec Mitchell.

Possibly you could backpatch that into 7.2, although I'd think an update
to 7.3.4 would be a more profitable use of time.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: Odd problem with performance in duplicate database
  2003-08-11 21:15 Odd problem with performance in duplicate database Josh Berkus <josh@agliodbs.com>
  2003-08-11 22:03 ` Re: Odd problem with performance in duplicate database Josh Berkus <josh@agliodbs.com>
  2003-08-11 22:42   ` Re: Odd problem with performance in duplicate database Ron Johnson <ron.l.johnson@cox.net>
  2003-08-11 22:48     ` Re: Odd problem with performance in duplicate database Josh Berkus <josh@agliodbs.com>
  2003-08-11 22:51       ` Re: Odd problem with performance in duplicate database Peter Darley <pdarley@kinesis-cem.com>
  2003-08-12 00:29         ` Re: Odd problem with performance in duplicate database Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-08-12 00:43           ` Josh Berkus <josh@agliodbs.com>
  2003-08-12 13:11             ` Re: Odd problem with performance in duplicate database Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 1 reply; 14+ messages in thread

From: Josh Berkus @ 2003-08-12 00:43 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance

Tom,

> Partly.  The numbers suggest that in ANALYZE's default sample of 3000
> rows, it's only finding about a dozen non-null tgroup_ids (yielding the
> 0.996 null_frac value); and that in one case all dozen are different and
> in the other case there are two duplicates.  It would help if you
> boosted the stats target for this column by a factor of 10.  (You can
> do that in 7.2, btw --- IIRC the only problem is that a pg_dump won't
> show that you did so.)

Also, there doesn't seem to be any way in 7.2 for me to find out what the 
current statistics target for a column is.   What am I missing?

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco




^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: Odd problem with performance in duplicate database
  2003-08-11 21:15 Odd problem with performance in duplicate database Josh Berkus <josh@agliodbs.com>
  2003-08-11 22:03 ` Re: Odd problem with performance in duplicate database Josh Berkus <josh@agliodbs.com>
  2003-08-11 22:42   ` Re: Odd problem with performance in duplicate database Ron Johnson <ron.l.johnson@cox.net>
  2003-08-11 22:48     ` Re: Odd problem with performance in duplicate database Josh Berkus <josh@agliodbs.com>
  2003-08-11 22:51       ` Re: Odd problem with performance in duplicate database Peter Darley <pdarley@kinesis-cem.com>
  2003-08-12 00:29         ` Re: Odd problem with performance in duplicate database Tom Lane <tgl@sss.pgh.pa.us>
  2003-08-12 00:43           ` Re: Odd problem with performance in duplicate database Josh Berkus <josh@agliodbs.com>
@ 2003-08-12 13:11             ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 14+ messages in thread

From: Tom Lane @ 2003-08-12 13:11 UTC (permalink / raw)
  To: josh@agliodbs.com; +Cc: pgsql-performance

Josh Berkus <josh@agliodbs.com> writes:
> Also, there doesn't seem to be any way in 7.2 for me to find out what the 
> current statistics target for a column is.   What am I missing?

There still isn't a handy command for it --- you have to look at
pg_attribute.attstattarget for the column.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: Odd problem with performance in duplicate database
  2003-08-11 21:15 Odd problem with performance in duplicate database Josh Berkus <josh@agliodbs.com>
  2003-08-11 22:03 ` Re: Odd problem with performance in duplicate database Josh Berkus <josh@agliodbs.com>
  2003-08-11 22:42   ` Re: Odd problem with performance in duplicate database Ron Johnson <ron.l.johnson@cox.net>
  2003-08-11 22:48     ` Re: Odd problem with performance in duplicate database Josh Berkus <josh@agliodbs.com>
  2003-08-11 22:51       ` Re: Odd problem with performance in duplicate database Peter Darley <pdarley@kinesis-cem.com>
  2003-08-12 00:29         ` Re: Odd problem with performance in duplicate database Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-08-12 00:51           ` Josh Berkus <josh@agliodbs.com>
  1 sibling, 0 replies; 14+ messages in thread

From: Josh Berkus @ 2003-08-12 00:51 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance

Tom,

> Partly.  The numbers suggest that in ANALYZE's default sample of 3000
> rows, it's only finding about a dozen non-null tgroup_ids (yielding the
> 0.996 null_frac value); and that in one case all dozen are different and
> in the other case there are two duplicates.  It would help if you
> boosted the stats target for this column by a factor of 10.  (You can
> do that in 7.2, btw --- IIRC the only problem is that a pg_dump won't
> show that you did so.)

Hmmm.  No dice.   I raised the selectivity to 1000, which increased n_distinct 
to 108, which is pretty close to accurate.  However, the planner still 
insists on using a seq scan on case_clients unless I drop random_page_cost to 
1.5 (which is up from 1.2 but still somewhat unreasonable).

> But the other part of the problem is that in 7.2, the join selectivity
> estimator is way off when you are joining a unique column (like the pkey
> on the other side) to a column with a very large fraction of nulls.
> We only discovered this recently; it's fixed as of 7.3.3:

OK, I'll talk to the client about upgrading.

-- 
-Josh Berkus

______AGLIO DATABASE SOLUTIONS___________________________
                                        Josh Berkus
   Complete information technology 	josh@agliodbs.com
    and data management solutions 	(415) 565-7293
   for law firms, small businesses 	 fax 621-2533
    and non-profit organizations. 	San Francisco




^ permalink  raw  reply  [nested|flat] 14+ messages in thread


end of thread, other threads:[~2003-08-12 13:11 UTC | newest]

Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-08-11 21:15 Odd problem with performance in duplicate database Josh Berkus <josh@agliodbs.com>
2003-08-11 22:03 ` Josh Berkus <josh@agliodbs.com>
2003-08-11 22:42   ` Ron Johnson <ron.l.johnson@cox.net>
2003-08-11 22:48     ` Josh Berkus <josh@agliodbs.com>
2003-08-11 22:51       ` Peter Darley <pdarley@kinesis-cem.com>
2003-08-11 22:59         ` Josh Berkus <josh@agliodbs.com>
2003-08-11 23:22           ` Tom Lane <tgl@sss.pgh.pa.us>
2003-08-11 23:46             ` Tom Lane <tgl@sss.pgh.pa.us>
2003-08-11 23:51             ` Josh Berkus <josh@agliodbs.com>
2003-08-11 23:59               ` Tom Lane <tgl@sss.pgh.pa.us>
2003-08-12 00:29         ` Tom Lane <tgl@sss.pgh.pa.us>
2003-08-12 00:43           ` Josh Berkus <josh@agliodbs.com>
2003-08-12 13:11             ` Tom Lane <tgl@sss.pgh.pa.us>
2003-08-12 00:51           ` Josh Berkus <josh@agliodbs.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