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
=:->