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 =:->
signature.asc
Description: OpenPGP digital signature
