Why one database is so much slower than other? The same query on postgresql takes 9 min on one database, on other only 5 sec.

I use the same postmaser, the same user. I connect directly with psql.

There seems to be no differences in database settings:

psql dbone -c 'SELECT * FROM pg_settings;' &gt; pg_settings_dbone.txt
psql dbtwo -c 'SELECT * FROM pg_settings;' &gt; pg_settings_dbtwo.txt
diff pg_settings_dbone.txt pg_settings_dbtwo.txt


EIDT:

Databases should be the same (scheduled copy)

query

explain analyze select clinic.code_8, 
    (select count(distinct visit.id)
            from patients lpatient 
            join visits_patients vp on (vp.hq_id = lpatient.hq_id and vp.patient_queue_id = lpatient.ext_id and vp.status='N' and vp.state &lt;&gt; 'C')
            join visits visit on (visit.hq_id = vp.hq_id and visit.ext_id = vp.visit_id and visit.state &lt;&gt; 'C' and date_trunc('day', visit.start) = date_trunc('day', slot.slot_start))
            join timetables vtimetable on (vtimetable.hq_id = visit.hq_id and vtimetable.ext_id = visit.timetable_id)
            join clinics vclinic on (vclinic.hq_id = vtimetable.hq_id and vclinic.ext_id = vtimetable.clinic_id and vclinic.code_8 = clinic.code_8)
        where ((lpatient.hq_id, lpatient.ext_id) in (select hq_id, ext_id from patient_links where patient_id = patient.id))),
    (select count(distinct visit.id)
            from patients lpatient 
            join visits_patients vp on (vp.hq_id = lpatient.hq_id and vp.patient_queue_id = lpatient.ext_id and vp.status='N' and vp.state &lt;&gt; 'C')
            join visits visit on (visit.hq_id = vp.hq_id and visit.ext_id = vp.visit_id and visit.state='N')
            join timetables vtimetable on (vtimetable.hq_id = visit.hq_id and vtimetable.ext_id = visit.timetable_id)
            join clinics vclinic on (vclinic.hq_id = vtimetable.hq_id and vclinic.ext_id = vtimetable.clinic_id and vclinic.code_8 = clinic.code_8)
        where (lpatient.hq_id, lpatient.ext_id) in (select hq_id, ext_id from patient_links where patient_id = patient.id)),
        stype.allow_series
from patients patient,
timetable_slots slot
    join visit_types stype on (stype.hq_id = slot.hq_id and stype.ext_id = slot.visit_type_id and stype.status='N')
    join timetables timetable on (timetable.hq_id = slot.hq_id and timetable.ext_id = slot.timetable_id)
    join clinics clinic on (clinic.hq_id = timetable.hq_id and clinic.ext_id = timetable.clinic_id)
where patient.hq_id = 1 and patient.ext_id = 234223 and slot.hq_id = 3 and slot.ext_id = 313245


fast query http://explain.depesz.com/s/jjC2

slow query http://explain.depesz.com/s/e26V

EDIT2:

Somehow indexes where corrupted on second db. Second db was copied without indexes?
There could be a bunch of reasons.  Chances are that the data is different in the database and the planner is generating different query plans as a result.  Other differences may include caching differences (maybe the data in one query is in the cache and the other one is not?)....

There are two approaches you can take to troubleshooting here.  The first is to only look at performance when we know the data is cached to the extent possible and the second is to only look at performance when we know the data is not cached.  To do the first, you run each query twice and discard the timing on the first result.  To do the second, we cat a hard drive to /dev/null between each run.   I mention this though because it is very important to compare apples to apples here.  If you find that caching is the whole difference (which it probably is not) then you know the issue is in usage patterns in the two dbs.  So assuming we are looking at cache-active responses, here's my suggestion:


Run each query once by itself, followed by EXPLAIN ANALYZE [query] and post the explain analyze results in your question if you need further help (the first run is just to ensure caches are being used to their fullest).  Note you need to post the query along with the query plan for us to make much sense of it.
A very useful tool for looking at explain output is http://explain.depesz.com/ and I would suggest making use of that too.
In the explain output look for sequential scans returning a large number of rows that should be unnecessary (because only a few rows are searched for) and mismatches between estimates and actual return values.  For seq scans returning many more rows than needed (say more than 10x as much), then chances are the query might need some tuning or an index might solve the problem.


A second point is that a 5 sec query IME is usually a reporting query of some sort (people don't like to wait 5 sec for data entry).  If this is the case, then it may be aggregating a fair bit of data, and the data set size may be much larger on the db which is posing the problem.  More memory can obviously help here but indexes only get you so far before a physical order search is quite a bit faster.  I have a few 5-15 sec data entry queries but they are rare and much more like reporting queries ("show me all unpaid invoices between the following dates" which return thousands of rows for processing) and only 5-15 sec on db's with gigabytes of accounting data.  If 5 sec is sufficient it is usually either reporting or closely related.