From: | Sergey Konoplev <gray(dot)ru(at)gmail(dot)com> |
---|---|
To: | Дмитрий Шалашов <skaurus(at)gmail(dot)com> |
Cc: | "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: trick the query optimiser to skip some optimisations |
Date: | 2014-01-30 20:36:26 |
Message-ID: | CAL_0b1sZhhFZ_6Ot4=WepUf6tFtpZOTNpsdHWH1FFCMwuzon-Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On Wed, Jan 29, 2014 at 3:38 PM, Дмитрий Шалашов <skaurus(at)gmail(dot)com> 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(dot)ru(at)gmail(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Jeff Janes | 2014-01-30 22:03:32 | Re: trick the query optimiser to skip some optimisations |
Previous Message | Dattaram Porob | 2014-01-30 17:43:37 | Re: Increased memory utilization by pgsql backend after upgrade from 9.1.3 to 9.2.6 |