Re: Design advice requested

On 09/05/13 17:42, Johann Spies wrote:
> Hallo Julian,
>
> Thanks for your reply.
>
> Firstly, don't worry too much about speed in the design phase,
> there may
> be differences of opinion here, but mine is that even with database
> design the first fundamental layer is the relationship model.
>
>
> It is good to hear but when a simple query requesting 20 records takes
> nearly 7 minutes to complete, it becomes nearly unusable.

Hi, can you reply to the list?
This is a performance question now. You might want to start a new thread on
your performance issues.
Have you utilized indexes? (refer to Hash Cond)
Hash Cond: ((rauthor.rart_id)::text = (ritem.ut)::text)

look at "actual time=start..finish" on the planner process blocks and
also the
finish time of the preceding block.

Buffers: shared hit=104662 read=4745067, temp read=1006508 written=1006446

Give the specs of your hardware. More experience people will be able
to advise on getting the best out of this query.

Once you are reasonably happy with your schema you might want
to consider partitioning the larger datasets. You can also cache (temp)
blocks of data how you see appropriate (snapshots).

http://www.postgresql.org/docs/9.2/static/ddl-partitioning.html

Regards.
Julian.

--
Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general