From: Scott Marlowe <smarlowe@g2switchworks.com>
To: Saad Anis <saad.anis@comtechmobile.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Breaking up a query
Date: Tue, 15 Aug 2006 09:16:56 -0500
Message-ID: <1155651416.20252.181.camel@state.g2switchworks.com> (raw)
In-Reply-To: <C1013130.34CA7%saad.anis@comtechmobile.com>
References: <C1013130.34CA7%saad.anis@comtechmobile.com>
On Thu, 2006-08-10 at 17:53, Saad Anis wrote:
> Hi Guys,
>
> A fellow at work has written the SQL below to retrieve some data from
> multiple tables. Obviously it is inefficient and unnecessarily complex, and
> I am trying to break it into 2 or more queries so as to enhance performance.
Nope, that's not true in PostgreSQL. It is for some databases with
relatively simplistic query planners, but not postgresql.
I'd check that you have indexes where you need them (generally when you
see a seq scan on a small set) including, especially, the foreign key
columns (i.e. the ones pointing to another table's primary keys).
On to your explain analyze, I noticed a lot of lines like this:
Index Scan using positions_pkey on positions p (cost=0.00..32.00
rows=1000 width=28) (actual time=0.019..90.920 rows=13958 loop
seeing as how the statistical default for a new, unanalyzed table is
1000, and you've got 1000 peppered all through your explain analyze, I'd
guess you've not analyzed your database. Which means you've likely not
read the admin docs. which means you've likely not vacuumed the
database.
Read the admin docs (they're not that thick, and there's lots of good
info in there) and apply things like vacuum and analyze, and get back to
us on how things are doing then.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: smarlowe@g2switchworks.com, saad.anis@comtechmobile.com
Subject: Re: Breaking up a query
In-Reply-To: <1155651416.20252.181.camel@state.g2switchworks.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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