[PERFORM] PostgreSQL 9.3.2 Performance tuning for 32 GB server

Hi All, I have configured the blow parameters for a 32 GB server . I this correct ? shared_buffers = 6GB work_mem = 24MB maintenance_work_mem = 250MB effective_cache_size = 16GB shared_preload_libraries = 'pg_stat_statements' pg_stat_statements.max = 1 pg_stat_statements.track = all wal_buffe

[PERFORM] Slow query on join with Date >=

I have a performance problem using a dimensional model where the date is specified in a DATE dimension, specifically when using 'WHERE DATE >= 'Some Date' This query runs very fast when using an equality expression, eg. 'WHERE DATE = '2014-01-01", and I'm wondering if there is a way to make it run

Re: [PERFORM] WHERE with ORDER not using the best index

Thank you so much for the prompt reply, Tom. The index is actually fairly new - but to be safe I issued REINDEX TABLE so that they are all clean. Here are the sizes of each index right after REINDEX. db=> select pg_size_pretty(pg_relation_size('index_social_feed_feed_items_on_social_feed_id_and_po

Re: [PERFORM] trick the query optimiser to skip some optimisations

Thanks for the tip! Well, index is now used but... Limit (cost=264291.67..264291.75 rows=31 width=50) -> Sort (cost=264291.67..264292.80 rows=453 width=50) Sort Key: added -> Bitmap Heap Scan on feed (cost=1850.99..264278.18 rows=453 width=50) Recheck Con

Re: [PERFORM] trick the query optimiser to skip some optimisations

On Wed, Jan 29, 2014 at 3:38 PM, Дмитрий Шалашов wrote: > "feed_user_id_added_idx2" btree (user_id, added DESC) WHERE active_id = > user_id AND type = 1 > ... > SELECT * FROM feed WHERE user_id = ? AND type = 1 AND active_id = user_id > ORDER BY added DESC LIMIT 31; > > But it doesn't use th

[PERFORM] trick the query optimiser to skip some optimisations

Hi! I have a table called 'feed'. It's a big table accessed by many types of queries, so I have quite a lot of indices on it. Those that are relevant looks like this: "feed_user_id_active_id_added_idx" btree (user_id, active_id, added) "feed_user_id_added_idx" btree (user_id, added DESC) "feed_u

Re: [PERFORM] WHERE with ORDER not using the best index

jugnooken writes: > Here's the query: > db=> EXPLAIN ANALYSE SELECT social_feed_feed_items.social_message_id FROM > social_feed_feed_items WHERE social_feed_feed_items.social_feed_id = 480 > ORDER BY posted_at DESC NULLS LAST LIMIT 1200;

Re: [PERFORM] Select hangs and there are lots of files in table and index directories.

Peter Blair writes: > One other problem with this case, those 900K worth of files in each of the > table and index directories (1.8M total files) are still hanging around. Hm ... if left to its own devices, I think the session that created them should have deleted them, assuming you did a normal

[PERFORM] WHERE with ORDER not using the best index

Hello everyone, I've a query that runs on a table with a matching index to its WHERE and ORDER clause. However the planner never uses that index. Is there any reason why it doesn't? Here's the table: db=> \d social_feed_feed_items; Table "public.social_fe

Re: [PERFORM] Select hangs and there are lots of files in table and index directories.

All, One other problem with this case, those 900K worth of files in each of the table and index directories (1.8M total files) are still hanging around. I have: * fixed the and reloaded the stored procedure * restarted the database * ran the stored procedure * there are only 378 rows in the pg_cl