pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: John Arbash Meinel <john@arbash-meinel.com>
To: Joel Fradkin <jfradkin@wazagua.com>
Cc: 'Tom Lane' <tgl@sss.pgh.pa.us>
Cc: 'PostgreSQL Perform' <pgsql-performance@postgresql.org>
Subject: Re: Any way to speed this up?
Date: Thu, 07 Apr 2005 11:43:02 -0500
Message-ID: <42556316.8020304@arbash-meinel.com> (raw)
In-Reply-To: <006701c53b8f$9274fdf0$797ba8c0@jfradkin>
References: <006701c53b8f$9274fdf0$797ba8c0@jfradkin>

Joel Fradkin wrote:

>shared_buffers = 8000		# min 16, at least max_connections*2, 8KB
>each
>work_mem = 8192#1024		# min 64, size in KB
>max_fsm_pages = 30000		# min max_fsm_relations*16, 6 bytes each
>effective_cache_size = 40000 #1000	# typically 8KB each
>random_page_cost = 1.2#4		# units are one sequential page
>fetch cost
>
>These are the items I changed.
>In the development box I turned random page cost to .2 because I figured it
>would all be faster using an index as all my data is at a minimum being
>selected by clientnum.
>
>
You're random page cost is *way* too low. I would probably change this
to no less that 2.0.

>But the analyze I sent in is from these settings above on a windows box.
>If I was running the analyze (pgadmin) on a windows box but connecting to a
>linux box would the times be accurate or do I have to run the analyze on the
>linux box for that to happen?
>
>
>
EXPLAIN ANALYZE is done on the server side, so it doesn't matter what
you use to connect to it. The \timing flag occurs on the local side, and
is thus influenced my network latency (but it only tells you the time
for the whole query anyway).

>I am a little unclear why I would need an index on associate by location as
>I thought it would be using indexes in location and jobtitle for their
>joins.
>I did not say where locationid = x in my query on the view.
>I have so much to learn about SQL.
>Joel
>
>
> CREATE OR REPLACE VIEW viwassoclist AS
>  SELECT a.clientnum, a.associateid, a.associatenum, a.lastname,
> a.firstname, jt.value AS jobtitle, l.name AS "location", l.locationid
> AS mainlocationid, l.divisionid, l.regionid, l.districtid,
> (a.lastname::text || ', '::text) || a.firstname::text AS assocname,
> a.isactive, a.isdeleted
>    FROM tblassociate a
>    LEFT JOIN tbljobtitle jt ON a.jobtitleid = jt.id AND
> jt.clientnum::text = a.clientnum::text AND 1 = jt.presentationid
>    JOIN tbllocation l ON a.locationid = l.locationid AND
> l.clientnum::text = a.clientnum::text;

                   ^^^^^^^^^^^^^^^^^^^
The locationid is defined in your view. This is the part that postgres
uses to merge all of the different tables together, it doesn't really
matter whether you restrict it with a WHERE clause.

Try just setting your random page cost back to something more
reasonable, and try again.

John
=:->

Attachments:

  signature.asc (application/pgp-signature, 252B) download

view thread (11+ messages)  latest in thread

Message-ID: <42556316.8020304@arbash-meinel.com>
Permalink:  ../42556316.8020304@arbash-meinel.com/
Also on:    postgresql.org/message-id/42556316.8020304@arbash-meinel.com

 · 

reply

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-performance@postgresql.org
  Cc: john@arbash-meinel.com, jfradkin@wazagua.com, tgl@sss.pgh.pa.us
  Subject: Re: Any way to speed this up?
  In-Reply-To: <42556316.8020304@arbash-meinel.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