From: | Jeff Janes <jeff(dot)janes(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 22:03:32 |
Message-ID: | CAMkU=1yvcj2cWUTG_Y4aOZXZkMiqBmCbg_cCQP5EtJRqUXABeg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On Wed, Jan 29, 2014 at 4:17 PM, Дмитрий Шалашов <skaurus(at)gmail(dot)com> wrote:
> 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 Cond: ((active_id = user_id) AND (type = 1))
> Filter: ((user_id + 0) = 7)
> -> Bitmap Index Scan on feed_user_id_added_idx2
> (cost=0.00..1850.88 rows=90631 width=0)
>
Ah, of course. It prevents the optimization you want, as well as the one
you don't want.
This is getting very ugly, but maybe change the index to match the
degenerate query:
"feed_user_id_added_idx3" btree ((user_id + 0), added DESC) WHERE active_id
= user_id AND type = 1
Long term I would probably look into refactoring the table so that
"active_id = user_id" is not a magical condition, like it seems to be for
you currently. Maybe introduce a boolean column.
Cheers,
Jeff
From | Date | Subject | |
---|---|---|---|
Next Message | Julien Cigar | 2014-01-31 13:55:04 | Re: PostgreSQL 9.3.2 Performance tuning for 32 GB server |
Previous Message | Sergey Konoplev | 2014-01-30 20:36:26 | Re: trick the query optimiser to skip some optimisations |