Re: LIMIT clause slowing down query in some cases, accelerating in others

On 04/26/2017 08:11 PM, Klaus P. Pieper wrote:
>
> Running PostgreSQL 9.6 on a Windows Server.
>
> Table “t” is kind of a materialized view with > 100 columns and 2.24
> Mio rows. Queries are generated by an ORM framework – fairly difficult
> to modify.
>
> Vacuum analyze was carried out – no impact.
>
>
>
> The framework generates queries like this:
>
>
>
> select N0."uorderid" from "t" N0
>
> where (N0."szzip" like E'33%')
>
> order by N0."szzip" asc nulls last LIMIT 128 OFFSET 0
>

The planner has to choose whether to use an index for filtering or an
index for sorting. If you're always doing prefix searches like in your
two examples, then you want an index which can do both.

CREATE INDEX ON t (szzip text_pattern_ops, uorderid);

I invite you to read the documentation about text_pattern_ops at
https://www.postgresql.org/docs/current/static/indexes-opclass.html

--
Vik Fearing +33 6 46 75 15 36
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support