Re: How to reduce impact of a query.

Craig Ringer wrote:
> Howard Cole wrote:
>
>
>> Unfortunately I am on a windows platform. Plus I am running windows
>> software raid so there is little tweaking allowed.
>>
> Don't write the possibility off too quickly. The driver may well accept
> parameters for readahead settings etc, either through a custom
> configuration applet (might be a separate download), the driver
> properties interface in Device Manager, or through manual registry settings.
>
>
>
Hmm. It would probably be more economical to buy the bigger server!
>> I didn't even know you could do that! I can do this on a system wide
>> basis for all of my read only queries so I shall see if it makes a
>> difference. I'll check the locking issues but I was under the impression
>> that postgres was excellent for this?
>>
>
> It generally is. You still need to think about how your concurrent
> queries interact, though, as there are some operations that really must
> be ordered. For example, if a query tries to UPDATE a record that a
> concurrent query has already UPDATEd PostgreSQL will make the second
> query block until the first one commits or rolls back. Doing otherwise
> would cause exciting issues if the second query's UPDATE was in any way
> dependent on the results of the first.
>
> If you're only INSERTing and SELECTing then there's not much that'll
> make queries interfere with each other with locks, unless you have
> trigger-maintained materialized views, summary tables or the like
> issuing UPDATEs behind the scenes.
>
>
You'll be pleased to know that changing the transaction to read only
(SET TRANSACTION READ ONLY)
as you suggested seemed to prevent the server from slowing to a halt.
The query still takes an age but not to the
detriment of all else. Many thanks for that tip.

Is it not possible that the query optimisation process should determine
that the query should be read only without
explicitly stating this?
>> One of the reqular, smaller
>> queries does however use the same table so I shall check if this is
>> having a major impact. - If I set them both to read-only then that might
>> have the desired impact?
>>
>
> I don't think it'll change anything much, but it might tell you (by
> causing a query to fail) if it's trying to write anything. This might
> help you detect a point at which the queries are interacting that you
> might not have expected, such as an UPDATE being issued within a trigger
> or by a rule.
>
>
I do have a trigger-on-update on this table, and it is possible that an
insert is being done in parallel.

However my immediate problems appears to be solved. Special thanks to
Craig and Scott.

Howard Cole
www.selestial.com