Re: What is the right way to deal with a table with rows that are not in a random order?

ORM should'nt be a factor as you have many configureable properties available in hibernate.cfg.xml or hibernate.properties
hibernate.connection.driver_classjdbc driver classhibernate.connection.urljdbc URLhibernate.connection.usernamedatabase userhibernate.connection.passworddatabase user passwordhibernate.connection.pool_sizemaximum number of pooled connections
Hibernate's own connection pooling algorithm is however quite rudimentary.
It is intended to help you get started and is not intended for use
in a production system or even for performance testing. You should
use a third party pool for best performance and stability. Just replace the
hibernate.connection.pool_size property with connection
pool specific settings. This will turn off Hibernate's internal pool. For
example, you might like to use C3P0.

C3P0 is an open source JDBC connection pool distributed along with
Hibernate in the lib directory. Hibernate will use its
C3P0ConnectionProvider for connection pooling if you set
hibernate.c3p0.* properties. If you'd like to use Proxool
refer to the packaged hibernate.properties and the Hibernate
web site for more information.

Here is an example hibernate.properties file for C3P0:

hibernate.connection.driver_class = org.postgresql.Driver
hibernate.connection.url = jdbc:postgresql://localhost/mydatabase
hibernate.connection.username = myuser
hibernate.connection.password = secret
hibernate.c3p0.min_size=5
hibernate.c3p0.max_size=20
hibernate.c3p0.timeout=1800
hibernate.c3p0.max_statements=50
hibernate.dialect = org.hibernate.dialect.PostgreSQLDialecthttps://www.hibernate.org/214.html

Martin Gainty
______________________________________________
Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité

Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen.
Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni.

> From: darkwater42(at)gmail(dot)com
> Date: Thu, 28 May 2009 15:03:32 -0400
> Subject: Re: [GENERAL] What is the right way to deal with a table with rows that are not in a random order?
> To: pgsql-general(at)postgresql(dot)org
> CC: simon(at)2ndquadrant(dot)com
>
> On Thu, May 28, 2009 at 4:14 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>
> > Partition the table, then scan the correct partition.
>
> If I do that, will Postgres figure out the "right thing" to do if the
> parent table is queried instead? Also, what are the performance
> implications then for doing queries that span all the partitions,
> which will be the norm for our application?
>
> The application in question is a kind of data warehousing thing (of
> astronomical stars), and there's an ORM in the middle, so it's not
> easy for us to hand-tune how individual queries are specified.
> Instead, we have to structure the database and the indexes so that
> things generally perform well, without having to tweak specific
> queries.
>
> Users can specify fairly arbitrary search criteria. All of the
> queries should perform well. By "well", I mean within 10 seconds or
> so. Scanning all of the 150 million rows takes much longer than 10
> seconds, unfortunately.
>
> Any one of these "solutions" will cause Postgres to do an index scan
> in the problematic case where Postgres is deciding to a sequential
> scan. The index scan performs snappily enough:
>
> - Using "order by" on the query.
>
> - Changing the search value for the column to a value that occurs
> less frequently.
>
> - Fetching the value to search for via a sub-query so that Postgres
> can't determine a priori that the
> value being searched value occurs so commonly.
>
> Unfortunately, as I mentioned, due to the ORM, none of these solutions
> really work for us in practice, as opposed to at a psql prompt.
>
> |>ouglas
>
> --
> 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

_________________________________________________________________
Hotmail® has ever-growing storage! Don’t worry about storage limits.
http://windowslive.com/Tutorial/Hotmail/Storage?ocid=TXT_TAGLM_WL_HM_Tutorial_Storage1_052009