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

[Vik Fearing <vik.fearing@xxxxxxxxxxxxxxx>]

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