Tagged Questions
5
votes
1answer
94 views
Determining which isolation level is appropriate
This is a homework question.
For the following transactions state the isolation level that will
maximize throughput without lowering the integrity of the database.
Explain the answer.
...
4
votes
1answer
84 views
Does the order of fields in SELECT query matter when using composite indexing?
I understand that the order of columns in the index itself matters immensely; however, what about the order of columns in the subsequent SELECT queries that make use of that index?
For example, if I ...
4
votes
2answers
230 views
Huge database logging of event type rows and ways to optimize it
We have a database that stores events. Events are generated at a 1000 per sec rate. We need to keep these events accessible for some years.
Usual use of these events is selecting some of them from the ...
4
votes
1answer
149 views
Is the optimisation fence behaviour of a CTE (WITH query) specified in the SQL:2008 standard? If so, where?
I see frequent references to WITH queries (common table expressions, or CTEs) acting as an optimisation fence, where the server isn't permitted to push filters down into the CTE queries, pull common ...
3
votes
3answers
130 views
Optimizing queries on a range of timestamps (two columns)
I use postgresql-9.1 with ubuntu 12.04.
I need to select records inside a range of time: my table time_limits has two timestamp fields and one property integer. Indeed there are other info columns ...
2
votes
1answer
87 views
Postgres: count(*) vs count(id)
I saw in the documentation the difference between count(*) and count(pk). I had been using count(pk) (where pk is a SERIAL PRIMARY KEY) not knowing about the existence of count(*).
My question is ...
2
votes
1answer
844 views
PostgreSQL tree structure and recursive CTE optimization
I'm trying to represent a tree structure in PostgreSQL (8.4) to be able to query the path from the root to a given node or to find all the nodes within a sub-branch.
Here is a test table:
CREATE ...
2
votes
0answers
119 views
Best book on query optimization, rewriting, refactoring [closed]
I have a specific problem: huge amounts of Web-related data in Postgres db. The queries running against the data are often very inefficient.
But in a more general sense, I need a book that explains ...
1
vote
3answers
179 views
PostgreSQL and query planner
I have question about query planner in PostgreSQL. I know, that this planner is taking data from pg_statistics, but... Can anyone tell me on what basis this planner is taking those informations to ...
1
vote
1answer
111 views
PostgreSQL 9.1 - Asynchroneous commits performance boost
During peak hours our users experience a lot of timeouts due to an overloaded database server. We are currently trying to make optimizations in order to cope with the high traffic. We read in the ...
1
vote
1answer
63 views
Correct use of VOLATILE COST (and ROWS) indications in Postgresql stored procedure
While looking at several examples of pl/python and pl/pgsql, I have seen many - but not all - using volatile cost.
ie:
CREATE OR REPLACE FUNCTION my_function()
RETURNS setof record AS
$BODY$
-- ...
1
vote
2answers
333 views
Fastest validation query in PostgreSQL
What is the fastest query in PostgreSQL, which I can use as a validationQuery binding a JNDI resource?
I though that SELECT 1 is the simplest, but in this document it is said that in PostgreSQL we ...
1
vote
1answer
22 views
How un-clustered is a CLUSTER USING table
I have some tables which benefit greatly from CLUSTER ON/CLUSTER USING in Postgres. Data accessed at the same time is "defragmented" into a small number of disk blocks:
# CLUSTER table USING ...
1
vote
1answer
83 views
Does PostgreSQL optimize queries in transaction?
In my app I need to make big imports from user files, and to achieve that all records are updated/created I do it inside transaction. But before it I need to update massive, already existing in DB, ...
1
vote
1answer
72 views
Optimising query on view that merges similar tables with a clear discriminator
Using PostgreSQL 8.4, I have a number of tables that have a very similar structure, but that belong to different categories:
CREATE TABLE table_a (
id SERIAL PRIMARY KEY,
event_time TIMESTAMP ...
