Thread: Performance

I come from a Sybase background and have just started working with
Postgre. Are there any books or tutorials that cover general performance
issues for beginners? I don't just want to start creating databases and
tables or writing mad queries without really understanding what is happening
in the background. I know quite a few tips and tricks for Sybase but I don't
think that much of it is relevant on Postgres. I would like to know how
the query optimizer works, i.e. does the order of tables in the from
clause make a difference in speed and how does indexing work on Postgres?
Any help pointing me in the right direction would be appreciated.

Many Thanks
Jeandre

On Friday 07 February 2003 01:41 pm, you wrote:
> I come from a Sybase background and have just started working with
> Postgre. Are there any books or tutorials that cover general performance
> issues for beginners? I don't just want to start creating databases and
> tables or writing mad queries without really understanding what is
> happening in the background. I know quite a few tips and tricks for Sybase
> but I don't think that much of it is relevant on Postgres. I would like to
> know how the query optimizer works, i.e. does the order of tables in the
> from clause make a difference in speed and how does indexing work on
> Postgres? Any help pointing me in the right direction would be appreciated.

Religously go thr. admin guide. and then a quick look thr. all SQL commands.
May take a day or two but believe me, it is worth that.

 Shridhar

jeandre@itvs.co.za writes:
> ... does the order of tables in the from
> clause make a difference in speed

No, it does not; except perhaps in corner cases where two tables have
exactly the same statistics, so that the planner has no basis for
choosing one over the other.  (This scenario could happen if you've
never ANALYZEd either, for example.)

> and how does indexing work on Postgres?

Uh, it indexes.  What's your question exactly?

            regards, tom lane

On Fri, 7 Feb 2003, Tom Lane wrote:

> Uh, it indexes.  What's your question exactly?

In the documentation I saw something about different index types. In
Sybase you create an index and that is it. How do you choose what type of
index to create on a column in Postgres?

jeandre@itvs.co.za writes:
> In the documentation I saw something about different index types. In
> Sybase you create an index and that is it. How do you choose what type of
> index to create on a column in Postgres?

There's an optional clause in the CREATE INDEX command --- I think
"USING access_method", but check the man page.

In practice, 99.44% of indexes are the default btree type, so you
usually don't need to think about it.  I'd only use a non-btree index
if I needed to index non-scalar data (arrays, geometric types, etc);
the GIST and RTREE index types are designed for those.

            regards, tom lane

On Fri, 7 Feb 2003, Tom Lane wrote:

> In practice, 99.44% of indexes are the default btree type, so you
> usually don't need to think about it.

Thanks for clearing that up, and everyone else that has responded. With
the help I am getting from you guys I should have a good clue of using the
database to it's utmost optimum very soon.

regards
Jeandre

Jean,

Also check out the many performance-related articles at:

http://techdocs.postgresql.org

--
Josh Berkus
Aglio Database Solutions
San Francisco