Re: Design advice requested - Mailing list pgsql-general

From Julian
Subject Re: Design advice requested
Date
Msg-id 518C3E33.7060505@internode.on.net
Whole thread Raw
In response to Design advice requested  (Johann Spies <johann.spies@gmail.com>)
List pgsql-general
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.



pgsql-general by date:

Previous
From: Larry Rosenman
Date:
Subject: Re: LONG delete with LOTS of FK's
Next
From: Brett Haydon
Date:
Subject: psql history on OSX terminal