Re: Different performance of two simple queries

On Tue, Nov 17, 2015 at 3:59 PM, petrov(dot)boris(dot)v(at)mail(dot)ru <
petrov(dot)boris(dot)v(at)mail(dot)ru> wrote:

> Hi all.
> Two queries return same result. The first one always takes about 7ms, the
> second 1.5ms.
>
> Query 1:
> -------------------------
> select
> c2c.position, c2c.category, c.*
> from categories_companies c2c
> join companies c on c2c.company = c.id
> where c2c.category ~ 'otdelka_i_remont.*'::lquery
> order by c2c.position, c.id
> limit 20 offset 1760;
> -------------------------
>
> Query 2:
> -------------------------
> with cte as (
> select c2c.position, c2c.company, c2c.category
> from categories_companies c2c
> where c2c.category ~ 'otdelka_i_remont.*'::lquery
> order by c2c.position, c2c.company, c2c.category
> limit 20 offset 1760
> )
> select c2c.position, c2c.category, c.*
> from cte c2c
> join companies c on c2c.company = c.id;
> -------------------------
>
> Indexes:
> - categories_companies.category (c2c.category) is of type ltree, indexed
> by both gist and btree
> - categories_companies (c2c) have composite PK of company and category
> - companies.id (c.id) is PK, no explicit indexes created
>
> Questions:
> 1. Is this is normal, or I done something incorrectly?
> 2. What can I do to make first query perform as fast as the second one?
>
> I would say it is normal. Please read this for explanation :
http://blog.2ndquadrant.com/postgresql-ctes-are-optimization-fences/
<http://blog.2ndquadrant.com/postgresql-ctes-are-optimization-fences/>

<http://blog.2ndquadrant.com/postgresql-ctes-are-optimization-fences/>
T <http://blog.2ndquadrant.com/postgresql-ctes-are-optimization-fences/>
hanks,
Jayadevan