Re: Cost based SELECT/UPDATE

On Thu, Sep 08, 2005 at 06:45:33AM +0400, 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)?

You can try running the select from a process that's niced via the OS;
some OS's will take nice into account when scheduling IO. But there is
currently no mechanism to provide this capability from within
PostgreSQL.

There should probably be a TODO for this, since it's something that's
asked about fairly often.

* Provide a means for individual queries to be run at a lower priority

While nice allows this for CPU-bound queries, it generally doesn't
work for I/O bound queries.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461