Re: Cost based SELECT/UPDATE

On 9/7/2005 10:45 PM, Leonid Safronie wrote:

> Hi, ppl
>
> Is there any way to do SELECTs with different priorities?
>
> Once a month I need to do some complex reports on table with over 7
> billion rows, which implies several nested SELECTS and grouping (query
> runs over 20 minutes on P4/2.4GHz). Concurrently, there are over 50
> processes updating tables in the same database, including table being
> SELECTed to do monthly report. The issue is that response time for
> these 50 processes is very important unlike for report generation, and
> time spent by these processes while report running is unacceptable for
> my production environment (response time grows from 1-3 seconds up to
> 1-2 minutes).
>
> Is there any way to give different priorities to different
> queries/transactions, as it's done for VACUUMing (vacuum_cost_*
> options in config file)?
>

Fiddling with Postgres backend priorities (nice) has bee beaten to death
before, and was allways rejected for good reasons.

Which Postgres version is this? Everything pre-8.0 will suffer from
complete cache eviction on sequential scans of huge tables.

Have you thought about replicating the database to a "reporting slave"?

Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #