Re: Different performance of two simple queries - Mailing list pgsql-novice

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?