pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
SQL Performance Guidelines
5+ messages / 4 participants
[nested] [flat]

* SQL Performance Guidelines
@ 2005-01-26 16:44  Van Ingen, Lane <lvaningen@ESNCC.com>
  0 siblings, 0 replies; 5+ messages in thread

From: Van Ingen, Lane @ 2005-01-26 16:44 UTC (permalink / raw)
  To: pgsql-performance

Does anybody know where I can lay my hands on some guidelines to get best SQL performance
out of PostgreSQL? We are about to get into a project that will be new from the ground up (and\we are using Postgres for the first time). Would like to share some guidelines with developers on best practices
in Postgres? Thanks for your help.


^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: SQL Performance Guidelines
@ 2005-01-26 18:27  Van Ingen, Lane <lvaningen@ESNCC.com>
  0 siblings, 1 reply; 5+ messages in thread

From: Van Ingen, Lane @ 2005-01-26 18:27 UTC (permalink / raw)
  To: Van Ingen, Lane <lvaningen@ESNCC.com>; pgsql-performance

Clarification: I am talking about SQL coding practices in Postgres (how to write queries for best 
results), not  tuning-related considerations (although that would be welcomed too).
 
-----Original Message----- 
From: pgsql-performance-owner@postgresql.org on behalf of Van Ingen, Lane 
Sent: Wed 1/26/2005 11:44 AM 
To: pgsql-performance@postgresql.org 
Cc: 
Subject: [PERFORM] SQL Performance Guidelines

Does anybody know where I can lay my hands on some guidelines to get best SQL performance
out of PostgreSQL? We are about to get into a project that will be new from the ground up (and\we are using Postgres for the first time). Would like to share some guidelines with developers on best practices
in Postgres? Thanks for your help.

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: SQL Performance Guidelines
@ 2005-01-27 08:02  Dustin Sallings <dustin@spy.net>
  parent: Van Ingen, Lane <lvaningen@ESNCC.com>
  0 siblings, 1 reply; 5+ messages in thread

From: Dustin Sallings @ 2005-01-27 08:02 UTC (permalink / raw)
  To: Van Ingen, Lane <lvaningen@ESNCC.com>; +Cc: pgsql-performance


On Jan 26, 2005, at 10:27, Van Ingen, Lane wrote:

> Clarification: I am talking about SQL coding practices in Postgres 
> (how to write queries for best
> results), not  tuning-related considerations (although that would be 
> welcomed too).

	Your question is a bit too vague.  At this point in your development, 
all that really can be said is to understand relational database 
concepts in general, and use explain a lot when developing queries.  
(Oh, and don't forget to analyze before asking specific questions).

> -----Original Message-----
> From: pgsql-performance-owner@postgresql.org on behalf of Van Ingen, 
> Lane
> Sent: Wed 1/26/2005 11:44 AM
> To: pgsql-performance@postgresql.org
> Cc:
> Subject: [PERFORM] SQL Performance Guidelines
>
> Does anybody know where I can lay my hands on some guidelines to get 
> best SQL performance
> out of PostgreSQL? We are about to get into a project that will be new 
> from the ground up (and\we are using Postgres for the first time). 
> Would like to share some guidelines with developers on best practices
> in Postgres? Thanks for your help.
>
> ---------------------------(end of 
> broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>
>
> ---------------------------(end of 
> broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>
--
SPY                      My girlfriend asked me which one I like better.
pub  1024/3CAE01D5 1994/11/03 Dustin Sallings <dustin@spy.net>
|    Key fingerprint =  87 02 57 08 02 D0 DA D6  C8 0F 3E 65 51 98 D8 BE
L_______________________ I hope the answer won't upset her. ____________




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: SQL Performance Guidelines
@ 2005-01-27 14:50  Mitch Pirtle <mitch.pirtle@gmail.com>
  parent: Dustin Sallings <dustin@spy.net>
  0 siblings, 1 reply; 5+ messages in thread

From: Mitch Pirtle @ 2005-01-27 14:50 UTC (permalink / raw)
  To: pgsql-performance

On Thu, 27 Jan 2005 00:02:29 -0800, Dustin Sallings <dustin@spy.net> wrote:
> 
> On Jan 26, 2005, at 10:27, Van Ingen, Lane wrote:
> 
> > Clarification: I am talking about SQL coding practices in Postgres
> > (how to write queries for best
> > results), not  tuning-related considerations (although that would be
> > welcomed too).
> 
>         Your question is a bit too vague.  At this point in your development,
> all that really can be said is to understand relational database
> concepts in general, and use explain a lot when developing queries.
> (Oh, and don't forget to analyze before asking specific questions).

I disagree - there are plenty of tricks that are PostgreSQL only, and
many people on this list have that knowledge but it is not documented
anywhere, or is hidden within thousands of mailing list posts.

For example, IIRC when joining an integer column with a SERIAL column,
you must expicitly cast it as an integer or the planner will not use
the indexes, right? (This is a guess, as I remember reading something
like this and thinking, "How in the world is someone supposed to
figure that out, even with EXPLAIN?")

There is another thread about how a query using a WHERE NOT NULL
clause is faster than one without.

These things are PostgreSQL specific, and documenting them would go a
long way towards educating the switchover crowd.

The closest thing I have seen to this is the PostgreSQL Gotchas page:

http://sql-info.de/postgresql/postgres-gotchas.html

HTH,

-- Mitch



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: SQL Performance Guidelines
@ 2005-01-27 15:19  Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  parent: Mitch Pirtle <mitch.pirtle@gmail.com>
  0 siblings, 0 replies; 5+ messages in thread

From: Christopher Kings-Lynne @ 2005-01-27 15:19 UTC (permalink / raw)
  To: Mitch Pirtle <mitch.pirtle@gmail.com>; +Cc: pgsql-performance

> For example, IIRC when joining an integer column with a SERIAL column,
> you must expicitly cast it as an integer or the planner will not use
> the indexes, right? (This is a guess, as I remember reading something
> like this and thinking, "How in the world is someone supposed to
> figure that out, even with EXPLAIN?")

That's not true at all.  Perhaps you're thinking about BIGSERIAL and 
int8 indexes - something that's been addressed in 8.0.

Chris



^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2005-01-27 15:19 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-01-26 16:44 SQL Performance Guidelines Van Ingen, Lane <lvaningen@ESNCC.com>
2005-01-26 18:27 Re: SQL Performance Guidelines Van Ingen, Lane <lvaningen@ESNCC.com>
2005-01-27 08:02 ` Dustin Sallings <dustin@spy.net>
2005-01-27 14:50   ` Mitch Pirtle <mitch.pirtle@gmail.com>
2005-01-27 15:19     ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox