Joel Fradkin wrote:

shared_buffers = 8000           # min 16, at least max_connections*2, 8KB
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
I did not say where locationid = x in my query on the view.
I have so much to learn about SQL.

CREATE OR REPLACE VIEW viwassoclist AS SELECT a.clientnum, a.associateid, a.associatenum, a.lastname, a.firstname, jt.value AS jobtitle, 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 = 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.


Attachment: signature.asc
Description: OpenPGP digital signature

Reply via email to