Thank you Jeff.
Jeff Janes wrote
> Did you force PostgreSQL to stop using the index on s.id? If not, do
> that. If so, please post the EXPLAIN (analyze) of the plan it does switch
> to.
Yes, this
finishes in 20 seconds, which is two times faster than *order by id asc*.
Query plan:
Jeff
On Mon, Mar 6, 2017 at 8:46 AM, twoflower wrote:
> Thank you Jeff.
>
> There are 7 million rows satisfying fk_id_client = 20045. There is an
> index on fk_id_client, now I added a composite (fk_id_client, id) index but
> that did not help.
>
With 7 million rows, you shouldn't expect any magic he
Thank you Jeff.
There are 7 million rows satisfying fk_id_client = 20045. There is an index
on fk_id_client, now I added a composite (fk_id_client, id) index but that
did not help.
I see the point of what you are saying, but still don't understand how these
two situations (*asc* vs. *desc*) are n
On Mon, Mar 6, 2017 at 6:22 AM, twoflower wrote:
> I have the following query
>
> select *
> from "JOB_MEMORY_STORAGE" st
> inner join "JOB_MEMORY" s on s.fk_id_storage = st.id
> where st.fk_id_client = 20045
> order by s.id asc limit 50
>
>
The query stops as soon as it finds 50 rows which mee
I have the following query
which takes 90 seconds to finish. *JOB_MEMORY* has 45 million rows,
*JOB_MEMORY_STORAGE* has 50 000 rows.
Query plan:
As you can see, it is indeed using an index *JOB_MEMORY_id_desc* in a
backward direction, but it is very slow.
When I change ordering to *desc* in the
