On Fri, Oct 21, 2005 at 05:25:22PM -0700, Edoceo Lists wrote: [summary of situation] > Some queries take more than five minutes to complete and I'm sad > about that. How can I make this faster? You might get more help on pgsql-performance, which is specifically for discussions of performance issues. > I could munge dates into integers if their faster, I'm OK with that. Let's identify the problem before thinking about possible solutions. > What can I tweak in the configuration file to speed things up? What version of PostgreSQL are you using? What operating system? What kind of hardware (CPU, disks, amount of memory, etc.)? Are you regularly vacuuming and analyzing all tables in the database? Have you tuned your configuration at all? What are the following settings? shared_buffers work_mem (8.x) or sort_mem (7.x) effective_cache_size random_page_cost You might want to read through a tuning guide like this one: http://www.powerpostgresql.com/PerfList > So when I say > select x,y,z from big_transaction_table where date>='10/2/2005' and > date<='10/4/2005' and transaction_status in (1,2,3) order by date; > it takes five+ minutes. Please post the EXPLAIN ANALYZE output (not just EXPLAIN) of this query; that'll show us what the query planner is doing and how accurate its row count estimates are. If the row counts are way off then you might see an improvement by increasing the statistics target for the date and maybe the transaction_status columns. Or you might just need to analyze the table to update its statistics, and possibly vacuum it to get rid of dead tuples. -- Michael Fuhr ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
