> >> Actually the plans are equal, so I suppose it depends on what were >> run first :). Slow query operates with data mostly on disk, while >> fast one with data in memory. >> >> yeah. maybe the easiest way, is to start a fresh session and fire the >> queries. > > > After the fresh start , the results obtained are : As Chetan Suttraway already pointed out, the execution plans are exactly the same. And by "excactly" I mean there's no difference in evaluating those two queries. The difference is due to cached data - not just in shared buffers (which will be lost of postgres restart) but also in filesystem cache (which is managed by kernel, not postgres). So the first execution had to load (some of) the data into shared buffers, while the second execution already had a lot of data in shared buffers. That's why the first query run in 7.7sec while the second 6.2sec. >> This seems a slight upper hand of the second query . Again, there's no difference between those two queries, they're exactly the same. It's just a matter of which of them is executed first. > Would it be possible to tune it further. I don't think so. The only possibility I see is to add a flag into page_content table, update it using a trigger (when something is inserted/deleted from clause2). Then you don't need to do the join. > My postgresql.conf parameters are as follows : ( Total RAM = 16 GB ) > > shared_buffers = 4GB > max_connections=700 > effective_cache_size = 6GB > work_mem=16MB > maintenance_mem=64MB > > I think to change > > work_mem=64MB > maintenance_mem=256MB > > Does it has some effects now. Generally a good idea, but we don't know if there are other processes running on the same machine and what kind of system is this (how many users are there, what kind of queries do they run). If there's a lot of users, keep work_mem low. If there's just a few users decrease max_connections and bump up work_mem and consider increasing shared_buffers. Maintenance_work_mem is used for vacuum/create index etc. so it really does not affect regular queries. Some of those values (e.g. work_mem/maintenance_work_mem) are dynamic, so you can set them for the current connection and see how it affects the queries. Just do something like db=# SET work_mem='32MB' db=# EXPLAIN ANALYZE SELECT ... But I don't think this will improve the query we've been talking about. regards Tomas -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
