Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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;' > pg_settings_dbone.txt
psql dbtwo -c 'SELECT * FROM pg_settings;' > 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 <> 'C')
            join visits visit on (visit.hq_id = vp.hq_id and visit.ext_id = vp.visit_id and visit.state <> '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 <> '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?

share|improve this question
5  
Please post the respective results of EXPLAIN ANALYZE. Underlying table definitions (with indexes) are also needed. –  dezso Feb 4 '13 at 14:20
3  
Please also read this: wiki.postgresql.org/wiki/SlowQueryQuestions –  a_horse_with_no_name Feb 4 '13 at 15:18
1  
Is the copy faster? Have you recently vacuumed your 'master' DB? –  dezso Feb 5 '13 at 8:23
    
What should I do with copy? copy those tables to files? –  rofrol Feb 5 '13 at 15:39
1  
If software, settings, schema and db contents are the same I would say it is a difference in the statistics as the two are using different plans. Run vacuum analyze or even vacuum full analyze to update the statistics. –  Eelke Feb 6 '13 at 19:01
add comment

2 Answers

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:

  1. 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.

  2. A very useful tool for looking at explain output is http://explain.depesz.com/ and I would suggest making use of that too.

  3. 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.

share|improve this answer
    
Does postgre use statistics like SQl server does? OUtdated statitistics are also a cause of this sort of thing in SQl Server. Missing indexes are also a potential cause. –  HLGEM Feb 7 '13 at 16:15
    
Yes, but chances are that if you don't have automatic processes running which perform db maintenance, you will see other problems first, like bloated tables. Typically in most cases statistics are gathered while looking through tables for free space that an be re-used. –  Chris Travers Feb 8 '13 at 0:57
add comment

I am adding a second answer here because the first answer is far more general in scope than this one, given the updates to the post.

There are a number of differences between the two query plans but both appear to be making use of appropriate indexes.

I am also ruling out temporarily the possibility of this being a statistics issue. While there are some inaccurate estimates, indexes are being used and consequently. I am also assuming that these are running on the same server. If any of these are incorrect, the solutions here may not be right.

Note you can check cache/buffer usage by:

EXPLAIN (analyze on, buffers on) [query]

This can be helpful in determining whether things like shared buffer or effective cache size settings should be raised or lowered.

My working theory is that the planner on the fast db (this is the production db, right?) knows that a lot of material there is cached in the Pg shared buffers but doesn't know that about the second db. Consequently it may be choosing a more conservative plan assuming less caching. I am basing this on the fact that the fast query prefers simple index scans while the slow one seems to be doing bitmap index scans, and that the join order appears to be different, changing which indexes are optimally used.

  1. Just to rule out statistics issues conclusively, run vacuum analyze on both dbs prior to further troubleshooting.

  2. You may try adjusting the effective_cache_size up, and check your shared_buffers to ensure they are high enough (but too high can be bad too). You might also try increasing modestly work_mem and see if that changes the performance profile (it is possible that reducing work_mem might help with this query too). Please note that increasing shared buffers can increase performance of your second db at the possible expense of the first because the filesystem cache is actually a bit faster than the Pg cache.

  3. While index corruption typically results in errors, rather than performance issues, if you want to rule this out, do reindex database [dbname]

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.