On Wed, Jan 29, 2014 at 3:38 PM, Дмитрий Шалашов <skaurus@xxxxxxxxx> wrote: > 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_user_id_added_idx2" btree (user_id, added DESC) WHERE active_id = > user_id AND type = 1 > > last one is very small and tailored for the specific query. > "added" field is timestamp, everything else is integers. [..] > Limit (cost=0.00..463.18 rows=31 width=50) > -> Index Scan Backward using feed_user_id_active_id_added_idx on > user_feed (cost=0.00..851.66 rows=57 width=50) > Index Cond: ((user_id = 7) AND (active_id = 7)) > Filter: (type = 1) [...] > Can I do something here so optimiser would use the feed_user_id_added_idx2 > index? It's around ten times smaller than the 'generic' > feed_user_id_active_id_added_idx index. > > I have PostgreSQL 9.2.6 on Debian. Could you please show EXPLAIN ANALYZE for both cases, the current one and with feed_user_id_active_id_added_idx dropped? -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979 gray.ru@xxxxxxxxx -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
