pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedquery plan worse after analyze
8+ messages / 6 participants
[nested] [flat]
* query plan worse after analyze
@ 2007-10-06 02:02 Jeff Frost <jeff@frostconsultingllc.com>
2007-10-06 03:14 ` Re: query plan worse after analyze Stephen Frost <sfrost@snowman.net>
2007-10-06 05:50 ` Re: query plan worse after analyze Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 8+ messages in thread
From: Jeff Frost @ 2007-10-06 02:02 UTC (permalink / raw)
To: pgsql-performance
Postgresql: 8.2.4 and 8.2.5
I've run into a really strange problem. I have a query that runs much
slower after analyze. I load the DB, then run the query and it runs in
about 200ms. I then analyze the DB, run the query again and it takes about
1500ms. Before analyze it seems to choose Bitmap Heap Scan on episodes
current_episode, but after it chooses Index Scan Backward using
index_episodes_on_publish_on on episodes current_episode. I've tried it on
two different pg servers and the results are same with default
random_page_cost and with random_page_cost = 2. The entire DB is only 8MB
so easily fits in memory on all the test systems. Any ideas what's going
on? Setting enable_indexscan = 0 also yields a fast (200ms) plan, so that's
a workaround.
Here are the plans:
jeff=# explain ANALYZE SELECT DISTINCT ON (shows.id) shows.id,
seasons.position AS alias_0 FROM shows
LEFT OUTER JOIN images ON images.id = shows.landing_page_image_id
LEFT OUTER JOIN seasons ON seasons.show_id = shows.id
LEFT OUTER JOIN episodes ON episodes.season_id = seasons.id AND
episodes.publish_on = (
SELECT MAX(current_episode.publish_on) AS publish_on FROM episodes AS
current_episode
WHERE current_episode.publish_on IS NOT NULL AND
current_episode.publish_on <= NOW()
AND current_episode.season_id = episodes.season_id
)
LEFT OUTER JOIN seasons current_seasons_shows ON
current_seasons_shows.show_id = shows.id AND seasons.position = (
SELECT MAX(latest_unvaulted_season.position) FROM seasons AS
latest_unvaulted_season, episodes
WHERE latest_unvaulted_season.show_id = seasons.show_id AND (
latest_unvaulted_season.vaults_on IS NULL OR
latest_unvaulted_season.vaults_on > NOW()
)
AND episodes.season_id = latest_unvaulted_season.id AND
episodes.publish_on IS NOT NULL
AND episodes.publish_on <= NOW()
)
LEFT OUTER JOIN episodes current_episodes_seasons ON
current_episodes_seasons.season_id = current_seasons_shows.id
AND episodes.publish_on = (
SELECT MAX(current_episode.publish_on) AS publish_on FROM episodes AS
current_episode
WHERE current_episode.publish_on IS NOT NULL AND
current_episode.publish_on <= NOW()
AND current_episode.season_id = episodes.season_id
)
WHERE (
( shows.deleted_at IS NULL OR shows.deleted_at > '2007-10-05
21:14:02.438466' )
AND ( shows.archived_at IS NULL OR shows.archived_at > '2007-10-05
21:14:02.438559' )
)
AND (episodes.id IS NOT NULL AND current_seasons_shows.id IS NOT NULL)
;
QUERY PLAN
----------------------------------------------------------------------------
--------------------------------------------------
----------------------------------------------------------------------------
--------------------------------------------------
-------
Unique (cost=106.63..106.64 rows=1 width=8) (actual time=178.931..181.528
rows=29 loops=1)
-> Sort (cost=106.63..106.64 rows=1 width=8) (actual
time=178.931..180.120 rows=6229 loops=1)
Sort Key: shows.id
-> Nested Loop Left Join (cost=2.27..106.62 rows=1 width=8)
(actual time=0.302..175.506 rows=6229 loops=1)
Join Filter: (episodes.publish_on = (subplan))
-> Nested Loop Left Join (cost=2.27..80.66 rows=1 width=24)
(actual time=0.262..21.725 rows=500 loops=1)
-> Nested Loop (cost=2.27..76.88 rows=1 width=28)
(actual time=0.221..19.641 rows=500 loops=1)
Join Filter: (current_seasons_shows.show_id =
shows.id)
-> Nested Loop (cost=2.27..74.55 rows=1
width=28) (actual time=0.215..10.716 rows=267 loops=1)
-> Nested Loop (cost=0.00..45.72 rows=1
width=20) (actual time=0.164..2.593 rows=29 loops=1
)
-> Seq Scan on shows
(cost=0.00..1.51 rows=4 width=8) (actual time=0.010..0.033 rows=
33 loops=1)
Filter: (((deleted_at IS NULL)
OR (deleted_at > '2007-10-05 21:14:02.438466'::tim
estamp without time zone)) AND ((archived_at IS NULL) OR (archived_at >
'2007-10-05 21:14:02.438559'::timestamp without time z
one)))
-> Index Scan using
index_seasons_on_show_id_and_position on seasons (cost=0.00..11.0
4 rows=1 width=12) (actual time=0.076..0.076 rows=1 loops=33)
Index Cond: (seasons.show_id =
shows.id)
Filter: ("position" =
(subplan))
SubPlan
-> Aggregate
(cost=9.26..9.27 rows=1 width=4) (actual time=0.040..0.040 rows=
1 loops=58)
-> Nested Loop
(cost=2.27..9.25 rows=1 width=4) (actual time=0.015..0.0
36 rows=12 loops=58)
-> Seq Scan on
seasons latest_unvaulted_season (cost=0.00..2.03 r
ows=1 width=8) (actual time=0.004..0.009 rows=1 loops=58)
Filter:
((show_id = $1) AND ((vaults_on IS NULL) OR (vaults_o
n > now())))
-> Bitmap Heap
Scan on episodes (cost=2.27..7.18 rows=3 width=4)
(actual time=0.009..0.019 rows=11 loops=62)
Recheck
Cond: (episodes.season_id = latest_unvaulted_season.i
d)
Filter:
((publish_on IS NOT NULL) AND (publish_on <= now()))
-> Bitmap
Index Scan on index_episodes_on_season_id (cost=0
.00..2.27 rows=3 width=0) (actual time=0.005..0.005 rows=12 loops=62)
Index
Cond: (episodes.season_id = latest_unvaulted_seas
on.id)
-> Bitmap Heap Scan on episodes
(cost=2.27..28.80 rows=3 width=12) (actual time=0.041..0.27
4 rows=9 loops=29)
Recheck Cond: (episodes.season_id =
seasons.id)
Filter: ((id IS NOT NULL) AND
(publish_on = (subplan)))
-> Bitmap Index Scan on
index_episodes_on_season_id (cost=0.00..2.27 rows=3 width=0)
(actual time=0.005..0.005 rows=11 loops=29)
Index Cond: (episodes.season_id
= seasons.id)
SubPlan
-> Aggregate (cost=7.20..7.21
rows=1 width=8) (actual time=0.022..0.022 rows=1 loop
s=324)
-> Bitmap Heap Scan on
episodes current_episode (cost=2.27..7.19 rows=1 width
=8) (actual time=0.007..0.016 rows=12 loops=324)
Recheck Cond:
(season_id = $0)
Filter: ((publish_on IS
NOT NULL) AND (publish_on <= now()))
-> Bitmap Index Scan
on index_episodes_on_season_id (cost=0.00..2.27 ro
ws=3 width=0) (actual time=0.004..0.004 rows=12 loops=324)
Index Cond:
(season_id = $0)
-> Seq Scan on seasons current_seasons_shows
(cost=0.00..1.59 rows=59 width=8) (actual time=0.002
..0.018 rows=59 loops=267)
Filter: (id IS NOT NULL)
-> Index Scan using images_pkey on images
(cost=0.00..3.77 rows=1 width=4) (actual time=0.003..0.003 ro
ws=1 loops=500)
Index Cond: (images.id =
shows.landing_page_image_id)
-> Index Scan using index_episodes_on_season_id on episodes
current_episodes_seasons (cost=0.00..4.30 rows=3
width=4) (actual time=0.002..0.009 rows=12 loops=500)
Index Cond: (current_episodes_seasons.season_id =
current_seasons_shows.id)
SubPlan
-> Aggregate (cost=7.20..7.21 rows=1 width=8) (actual
time=0.022..0.022 rows=1 loops=6229)
-> Bitmap Heap Scan on episodes current_episode
(cost=2.27..7.19 rows=1 width=8) (actual time=0.007..
0.016 rows=13 loops=6229)
Recheck Cond: (season_id = $0)
Filter: ((publish_on IS NOT NULL) AND
(publish_on <= now()))
-> Bitmap Index Scan on
index_episodes_on_season_id (cost=0.00..2.27 rows=3 width=0) (actual ti
me=0.004..0.004 rows=13 loops=6229)
Index Cond: (season_id = $0)
Total runtime: 181.829 ms
(51 rows)
Now, analyze comes along and updates the statistics for me:
jeff=# ANALYZE ;
ANALYZE
QUERY
PLAN
----------------------------------------------------------------------------
--------------------------------------------------
----------------------------------------------------------------------------
--------------------------------------------------
-
Unique (cost=0.00..1226.04 rows=1 width=8) (actual time=1.121..1472.459
rows=29 loops=1)
-> Nested Loop Left Join (cost=0.00..1226.03 rows=1 width=8) (actual
time=1.120..1470.904 rows=6229 loops=1)
Join Filter: (episodes.publish_on = (subplan))
-> Nested Loop Left Join (cost=0.00..1156.52 rows=1 width=24)
(actual time=0.662..99.337 rows=500 loops=1)
-> Nested Loop (cost=0.00..1155.40 rows=1 width=28) (actual
time=0.655..97.353 rows=500 loops=1)
Join Filter: (current_seasons_shows.show_id = shows.id)
-> Nested Loop (cost=0.00..1153.07 rows=1 width=28)
(actual time=0.652..88.459 rows=267 loops=1)
-> Nested Loop (cost=0.00..1062.02 rows=1
width=20) (actual time=0.089..3.140 rows=29 loops=1)
Join Filter: (seasons.show_id = shows.id)
-> Index Scan using
index_seasons_on_show_id_and_position on seasons (cost=0.00..1060.12 ro
ws=1 width=12) (actual time=0.065..2.466 rows=30 loops=1)
Filter: ("position" = (subplan))
SubPlan
-> Aggregate (cost=17.83..17.84
rows=1 width=4) (actual time=0.039..0.040 rows=1 lo
ops=59)
-> Nested Loop
(cost=2.34..17.80 rows=11 width=4) (actual time=0.016..0.035 r
ows=12 loops=59)
-> Seq Scan on seasons
latest_unvaulted_season (cost=0.00..2.03 rows=1
width=8) (actual time=0.004..0.009 rows=1 loops=59)
Filter: ((show_id
= $2) AND ((vaults_on IS NULL) OR (vaults_on > no
w())))
-> Bitmap Heap Scan on
episodes (cost=2.34..15.62 rows=12 width=4) (act
ual time=0.009..0.017 rows=11 loops=63)
Recheck Cond:
(episodes.season_id = latest_unvaulted_season.id)
Filter:
((publish_on IS NOT NULL) AND (publish_on <= now()))
-> Bitmap Index
Scan on index_episodes_on_season_id (cost=0.00..2
.34 rows=12 width=0) (actual time=0.006..0.006 rows=11 loops=63)
Index Cond:
(episodes.season_id = latest_unvaulted_season.id)
-> Seq Scan on shows (cost=0.00..1.51
rows=31 width=8) (actual time=0.002..0.013 rows=33 lo
ops=30)
Filter: (((deleted_at IS NULL) OR
(deleted_at > '2007-10-05 21:14:02.438466'::timestamp
without time zone)) AND ((archived_at IS NULL) OR (archived_at >
'2007-10-05 21:14:02.438559'::timestamp without time zone)))
-> Index Scan using index_episodes_on_season_id
on episodes (cost=0.00..91.04 rows=1 width=12) (a
ctual time=0.466..2.936 rows=9 loops=29)
-> Bitmap Index
Scan on index_episodes_on_season_id (cost=0.00..2
.34 rows=12 width=0) (actual time=0.006..0.006 rows=11 loops=63)
Index Cond:
(episodes.season_id = latest_unvaulted_season.id)
-> Seq Scan on shows (cost=0.00..1.51
rows=31 width=8) (actual time=0.002..0.013 rows=33 lo
ops=30)
Filter: (((deleted_at IS NULL) OR
(deleted_at > '2007-10-05 21:14:02.438466'::timestamp
without time zone)) AND ((archived_at IS NULL) OR (archived_at >
'2007-10-05 21:14:02.438559'::timestamp without time zone)))
-> Index Scan using index_episodes_on_season_id
on episodes (cost=0.00..91.04 rows=1 width=12) (a
ctual time=0.466..2.936 rows=9 loops=29)
Index Cond: (episodes.season_id =
seasons.id)
Filter: ((id IS NOT NULL) AND (publish_on =
(subplan)))
SubPlan
-> Result (cost=5.70..5.71 rows=1
width=0) (actual time=0.261..0.261 rows=1 loops=324)
InitPlan
-> Limit (cost=0.00..5.70
rows=1 width=8) (actual time=0.259..0.260 rows=1 loops=
324)
-> Index Scan Backward
using index_episodes_on_publish_on on episodes curren
t_episode (cost=0.00..62.72 rows=11 width=8) (actual time=0.258..0.258
rows=1 loops=324)
Index Cond:
(publish_on <= now())
Filter: ((publish_on
IS NOT NULL) AND (season_id = $0))
-> Seq Scan on seasons current_seasons_shows
(cost=0.00..1.59 rows=59 width=8) (actual time=0.002..0.01
7 rows=59 loops=267)
Filter: (id IS NOT NULL)
-> Index Scan using images_pkey on images (cost=0.00..1.11
rows=1 width=4) (actual time=0.002..0.003 rows=1 l
oops=500)
Index Cond: (images.id = shows.landing_page_image_id)
-> Index Scan using index_episodes_on_season_id on episodes
current_episodes_seasons (cost=0.00..0.77 rows=12 width
=4) (actual time=0.002..0.008 rows=12 loops=500)
Index Cond: (current_episodes_seasons.season_id =
current_seasons_shows.id)
SubPlan
-> Result (cost=5.70..5.71 rows=1 width=0) (actual
time=0.218..0.218 rows=1 loops=6229)
InitPlan
-> Limit (cost=0.00..5.70 rows=1 width=8) (actual
time=0.217..0.217 rows=1 loops=6229)
-> Index Scan Backward using
index_episodes_on_publish_on on episodes current_episode (cost=0.00..6
2.72 rows=11 width=8) (actual time=0.216..0.216 rows=1 loops=6229)
Index Cond: (publish_on <= now())
Filter: ((publish_on IS NOT NULL) AND
(season_id = $0))
Total runtime: 1472.613 ms
(47 rows)
set enable_indexscan = 0;
QU
ERY PLAN
----------------------------------------------------------------------------
--------------------------------------------------
----------------------------------------------------------------------------
--------------------------------------------------
-------
Unique (cost=1456.34..1456.35 rows=1 width=8) (actual
time=180.423..183.024 rows=29 loops=1)
-> Sort (cost=1456.34..1456.35 rows=1 width=8) (actual
time=180.422..181.624 rows=6229 loops=1)
Sort Key: shows.id
-> Nested Loop Left Join (cost=3.31..1456.33 rows=1 width=8)
(actual time=0.165..177.225 rows=6229 loops=1)
Join Filter: (episodes.publish_on = (subplan))
-> Nested Loop Left Join (cost=2.92..1265.33 rows=1
width=24) (actual time=0.133..22.742 rows=500 loops=1)
-> Nested Loop (cost=2.34..1262.73 rows=1 width=28)
(actual time=0.125..20.141 rows=500 loops=1)
Join Filter: (current_seasons_shows.show_id =
shows.id)
-> Nested Loop (cost=2.34..1260.40 rows=1
width=28) (actual time=0.122..11.087 rows=267 loops=1)
-> Nested Loop (cost=0.00..1056.47 rows=1
width=20) (actual time=0.087..2.966 rows=29 loops
=1)
Join Filter: (seasons.show_id =
shows.id)
-> Seq Scan on seasons
(cost=0.00..1054.57 rows=1 width=12) (actual time=0.063..2.309
rows=30 loops=1)
Filter: ("position" =
(subplan))
SubPlan
-> Aggregate
(cost=17.83..17.84 rows=1 width=4) (actual time=0.037..0.038 row
s=1 loops=59)
-> Nested Loop
(cost=2.34..17.80 rows=11 width=4) (actual time=0.014..0
.033 rows=12 loops=59)
-> Seq Scan on
seasons latest_unvaulted_season (cost=0.00..2.03 r
ows=1 width=8) (actual time=0.004..0.009 rows=1 loops=59)
Filter:
((show_id = $1) AND ((vaults_on IS NULL) OR (vaults_o
n > now())))
-> Bitmap Heap
Scan on episodes (cost=2.34..15.62 rows=12 width=4
) (actual time=0.008..0.016 rows=11 loops=63)
Recheck
Cond: (episodes.season_id = latest_unvaulted_season.i
d)
Filter:
((publish_on IS NOT NULL) AND (publish_on <= now()))
-> Bitmap
Index Scan on index_episodes_on_season_id (cost=0
.00..2.34 rows=12 width=0) (actual time=0.005..0.005 rows=11 loops=63)
Index
Cond: (episodes.season_id = latest_unvaulted_seas
on.id)
-> Seq Scan on shows
(cost=0.00..1.51 rows=31 width=8) (actual time=0.002..0.012 rows
=33 loops=30)
Filter: (((deleted_at IS NULL)
OR (deleted_at > '2007-10-05 21:14:02.438466'::tim
estamp without time zone)) AND ((archived_at IS NULL) OR (archived_at >
'2007-10-05 21:14:02.438559'::timestamp without time z
one)))
-> Bitmap Heap Scan on episodes
(cost=2.34..203.90 rows=3 width=12) (actual time=0.041..0.2
74 rows=9 loops=29)
Recheck Cond: (episodes.season_id =
seasons.id)
Filter: ((id IS NOT NULL) AND
(publish_on = (subplan)))
-> Bitmap Index Scan on
index_episodes_on_season_id (cost=0.00..2.34 rows=12 width=0)
(actual time=0.005..0.005 rows=11 loops=29)
Index Cond: (episodes.season_id
= seasons.id)
SubPlan
-> Aggregate (cost=15.68..15.69
rows=1 width=8) (actual time=0.022..0.022 rows=1 lo
ops=324)
-> Bitmap Heap Scan on
episodes current_episode (cost=2.34..15.65 rows=11 wid
th=8) (actual time=0.007..0.016 rows=12 loops=324)
Recheck Cond:
(season_id = $0)
Filter: ((publish_on IS
NOT NULL) AND (publish_on <= now()))
-> Bitmap Index Scan
on index_episodes_on_season_id (cost=0.00..2.34 ro
ws=12 width=0) (actual time=0.004..0.004 rows=12 loops=324)
Index Cond:
(season_id = $0)
-> Seq Scan on seasons current_seasons_shows
(cost=0.00..1.59 rows=59 width=8) (actual time=0.002
..0.018 rows=59 loops=267)
Filter: (id IS NOT NULL)
-> Bitmap Heap Scan on images (cost=0.58..2.59 rows=1
width=4) (actual time=0.003..0.003 rows=1 loops=5
00)
Recheck Cond: (images.id =
shows.landing_page_image_id)
-> Bitmap Index Scan on images_pkey
(cost=0.00..0.58 rows=1 width=0) (actual time=0.002..0.002 ro
ws=1 loops=500)
Index Cond: (images.id =
shows.landing_page_image_id)
-> Bitmap Heap Scan on episodes current_episodes_seasons
(cost=0.39..2.51 rows=12 width=4) (actual time=0.006
..0.010 rows=12 loops=500)
Recheck Cond: (current_episodes_seasons.season_id =
current_seasons_shows.id)
-> Bitmap Index Scan on index_episodes_on_season_id
(cost=0.00..0.39 rows=12 width=0) (actual time=0.00
4..0.004 rows=12 loops=500)
Index Cond: (current_episodes_seasons.season_id =
current_seasons_shows.id)
SubPlan
-> Aggregate (cost=15.68..15.69 rows=1 width=8) (actual
time=0.022..0.022 rows=1 loops=6229)
-> Bitmap Heap Scan on episodes current_episode
(cost=2.34..15.65 rows=11 width=8) (actual time=0.007
..0.016 rows=13 loops=6229)
Recheck Cond: (season_id = $0)
Filter: ((publish_on IS NOT NULL) AND
(publish_on <= now()))
-> Bitmap Index Scan on
index_episodes_on_season_id (cost=0.00..2.34 rows=12 width=0) (actual t
ime=0.004..0.004 rows=13 loops=6229)
Index Cond: (season_id = $0)
Total runtime: 183.160 ms
(55 rows)
----
Jeff Frost, Owner <jeff@frostconsultingllc.com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: query plan worse after analyze
2007-10-06 02:02 query plan worse after analyze Jeff Frost <jeff@frostconsultingllc.com>
@ 2007-10-06 03:14 ` Stephen Frost <sfrost@snowman.net>
2007-10-06 03:58 ` Re: query plan worse after analyze Rodrigo De León <rdeleonp@gmail.com>
2007-10-06 04:12 ` Re: query plan worse after analyze Jeff Frost <jeff@frostconsultingllc.com>
2007-10-06 15:11 ` Re: query plan worse after analyze Alvaro Herrera <alvherre@commandprompt.com>
2007-10-07 16:06 ` Re: query plan worse after analyze Richard Broersma Jr <rabroersma@yahoo.com>
1 sibling, 4 replies; 8+ messages in thread
From: Stephen Frost @ 2007-10-06 03:14 UTC (permalink / raw)
To: Jeff Frost <jeff@frostconsultingllc.com>; +Cc: pgsql-performance
* Jeff Frost (jeff@frostconsultingllc.com) wrote:
> Here are the plans:
It's probably just me but, honestly, I find it terribly frustrating to
try and read a line-wrapped explain-analyze output... I realize it
might not be something you can control in your mailer, but you might
consider putting the various plans up somewhere online (perhaps a
pastebin like http://pgsql.privatepaste.com) instead of or in addition
to sending it in the email.
Thanks!
Stephen
Attachments:
signature.asc (application/pgp-signature, 188B) download
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: query plan worse after analyze
2007-10-06 02:02 query plan worse after analyze Jeff Frost <jeff@frostconsultingllc.com>
2007-10-06 03:14 ` Re: query plan worse after analyze Stephen Frost <sfrost@snowman.net>
@ 2007-10-06 03:58 ` Rodrigo De León <rdeleonp@gmail.com>
3 siblings, 0 replies; 8+ messages in thread
From: Rodrigo De León @ 2007-10-06 03:58 UTC (permalink / raw)
To: pgsql-performance
On 10/5/07, Stephen Frost <sfrost@snowman.net> wrote:
> ... (perhaps a pastebin like http://pgsql.privatepaste.com) ...
This is cool.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: query plan worse after analyze
2007-10-06 02:02 query plan worse after analyze Jeff Frost <jeff@frostconsultingllc.com>
2007-10-06 03:14 ` Re: query plan worse after analyze Stephen Frost <sfrost@snowman.net>
@ 2007-10-06 04:12 ` Jeff Frost <jeff@frostconsultingllc.com>
3 siblings, 0 replies; 8+ messages in thread
From: Jeff Frost @ 2007-10-06 04:12 UTC (permalink / raw)
To: Stephen Frost <sfrost@snowman.net>; +Cc: pgsql-performance
On Fri, 5 Oct 2007, Stephen Frost wrote:
> * Jeff Frost (jeff@frostconsultingllc.com) wrote:
>> Here are the plans:
>
> It's probably just me but, honestly, I find it terribly frustrating to
> try and read a line-wrapped explain-analyze output... I realize it
> might not be something you can control in your mailer, but you might
> consider putting the various plans up somewhere online (perhaps a
> pastebin like http://pgsql.privatepaste.com) instead of or in addition
> to sending it in the email.
It's not you. In fact, after I sent this and saw what it looked like, I put
it into a txt file and replied with an attachment. Unfortunately, it didn't
bounce, nor did it show up on the list. :-(
So, here's a pastebin...it's a bit better:
http://pastebin.com/m4f0194b
--
Jeff Frost, Owner <jeff@frostconsultingllc.com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: query plan worse after analyze
2007-10-06 02:02 query plan worse after analyze Jeff Frost <jeff@frostconsultingllc.com>
2007-10-06 03:14 ` Re: query plan worse after analyze Stephen Frost <sfrost@snowman.net>
@ 2007-10-06 15:11 ` Alvaro Herrera <alvherre@commandprompt.com>
3 siblings, 0 replies; 8+ messages in thread
From: Alvaro Herrera @ 2007-10-06 15:11 UTC (permalink / raw)
To: Jeff Frost <jeff@frostconsultingllc.com>; pgsql-performance
Stephen Frost wrote:
> * Jeff Frost (jeff@frostconsultingllc.com) wrote:
> > Here are the plans:
>
> It's probably just me but, honestly, I find it terribly frustrating to
> try and read a line-wrapped explain-analyze output... I realize it
> might not be something you can control in your mailer, but you might
> consider putting the various plans up somewhere online (perhaps a
> pastebin like http://pgsql.privatepaste.com) instead of or in addition
> to sending it in the email.
I've considered writing a script to unwrap the stuff, but never got
around to doing it, because I need it quite often. (One problem is
dealing with the several different ways of wrapping).
If there are any takers I would be very thankful.
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: query plan worse after analyze
2007-10-06 02:02 query plan worse after analyze Jeff Frost <jeff@frostconsultingllc.com>
2007-10-06 03:14 ` Re: query plan worse after analyze Stephen Frost <sfrost@snowman.net>
@ 2007-10-07 16:06 ` Richard Broersma Jr <rabroersma@yahoo.com>
3 siblings, 0 replies; 8+ messages in thread
From: Richard Broersma Jr @ 2007-10-07 16:06 UTC (permalink / raw)
To: Stephen Frost <sfrost@snowman.net>; Jeff Frost <jeff@frostconsultingllc.com>; +Cc: pgsql-performance
--- Stephen Frost <sfrost@snowman.net> wrote:
> (perhaps a
> pastebin like http://pgsql.privatepaste.com) instead of or in addition
> to sending it in the email.
It this new? I don't remember seeing this used before.
Regards,
Richard Broersma Jr.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: query plan worse after analyze
2007-10-06 02:02 query plan worse after analyze Jeff Frost <jeff@frostconsultingllc.com>
@ 2007-10-06 05:50 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-10-06 06:41 ` Re: query plan worse after analyze Jeff Frost <jeff@frostconsultingllc.com>
1 sibling, 1 reply; 8+ messages in thread
From: Tom Lane @ 2007-10-06 05:50 UTC (permalink / raw)
To: Jeff Frost <jeff@frostconsultingllc.com>; +Cc: pgsql-performance
"Jeff Frost" <jeff@frostconsultingllc.com> writes:
> Before analyze it seems to choose Bitmap Heap Scan on episodes
> current_episode, but after it chooses Index Scan Backward using
> index_episodes_on_publish_on on episodes current_episode.
Have you tried raising the stats target for "episodes"? Seems like
the problem is a misestimate of the frequency of matches for
season_id = something.
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: query plan worse after analyze
2007-10-06 02:02 query plan worse after analyze Jeff Frost <jeff@frostconsultingllc.com>
2007-10-06 05:50 ` Re: query plan worse after analyze Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-10-06 06:41 ` Jeff Frost <jeff@frostconsultingllc.com>
0 siblings, 0 replies; 8+ messages in thread
From: Jeff Frost @ 2007-10-06 06:41 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
On Sat, 6 Oct 2007, Tom Lane wrote:
> "Jeff Frost" <jeff@frostconsultingllc.com> writes:
>> Before analyze it seems to choose Bitmap Heap Scan on episodes
>> current_episode, but after it chooses Index Scan Backward using
>> index_episodes_on_publish_on on episodes current_episode.
>
> Have you tried raising the stats target for "episodes"? Seems like
> the problem is a misestimate of the frequency of matches for
> season_id = something.
Can you set the stats target for an entire table up?
I tried this:
ALTER TABLE episodes ALTER COLUMN season_id SET STATISTICS 1000;
and got the same plan.
And since I had this on a test server, I set the default stats target
up to 100, reran analyze and got the same plan.
Same if I up it to 1000. :-(
--
Jeff Frost, Owner <jeff@frostconsultingllc.com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2007-10-07 16:06 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-10-06 02:02 query plan worse after analyze Jeff Frost <jeff@frostconsultingllc.com>
2007-10-06 03:14 ` Stephen Frost <sfrost@snowman.net>
2007-10-06 03:58 ` Rodrigo De León <rdeleonp@gmail.com>
2007-10-06 04:12 ` Jeff Frost <jeff@frostconsultingllc.com>
2007-10-06 15:11 ` Alvaro Herrera <alvherre@commandprompt.com>
2007-10-07 16:06 ` Richard Broersma Jr <rabroersma@yahoo.com>
2007-10-06 05:50 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-10-06 06:41 ` Jeff Frost <jeff@frostconsultingllc.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