Re: Tuning random_page_cost
От | Scott Marlowe |
---|---|
Тема | Re: Tuning random_page_cost |
Дата | |
Msg-id | 1089749059.3354.51.camel@localhost.localdomain обсуждение исходный текст |
Ответ на | Tuning random_page_cost ("Markus Wollny" <Markus.Wollny@computec.de>) |
Список | pgsql-general |
On Tue, 2004-07-13 at 05:30, Markus Wollny wrote: > Hi! > > I've got a query that has a where clause on a timestamp field: > > select t.board_id > , t.thread_id > from public.board_thread t > where t.last_reply <= now()-'6 months'::interval > limit 1 > > I've got random_page_cost set to 1.4 which is fine for most queries; > yet here the planner prefers a (slower) sequential scan: > > QUERY PLAN > Limit (cost=0.00..0.14 rows=1 width=8) (actual time=2.598..2.600 > rows=1 loops=1) > -> Seq Scan on board_thread t (cost=0.00..4613.87 rows=33674 > width=8) (actual time=2.592..2.592 rows=1 loops=1) > Filter: (last_reply <= (now() - '6 mons'::interval)) > Total runtime: 2.711 ms Did you happen to notice that the estimated number of rows is 33674 and the actual number is only 1? Are you analyzing this table, and if so, have you tried upping your target statistics on the column here? > When I set random_page_cost extremely low, the planner makes a better > decision; the "breaking point" ist at set random_page_cost = 0.16938, > which seems much to unrealistic to use as a general setting: Yes, but this is the wrong way. First, the planner needs the right numbers. Bonking it on the head with a sledge hammer is not how to get it to behave. Analyzing with a high enough stats target is. Note that these types of questions are generally better handled on the performance mailing list.
В списке pgsql-general по дате отправления: