LIMIT 1 poor query plan

From: Marcio Ribeiro <mribeiro(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: LIMIT 1 poor query plan
Date: 2015-10-10 08:52:35
Message-ID: CAMAHXRzu6daCPNtOGcGboTAsQ5gs9vdFEsTvACC2b6if9ni2-w@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hail there,

Short question:
Why would pg optimizer choose a worst (slower) query plan for a
query with 'LIMIT 1' instead of, say, 'LIMIT 3'?

Complete scenario:
Query: 'SELECT * FROM a WHERE a.b_id = 42 ORDER BY created LIMIT 1'
- b_id is a FK to b;
- created is a datetime with the time of the creation of the row;
- both 'b' and 'created' are indexed separately

This query, with the LIMIT 1, uses the index on created, which is much
slower (10x) than if it used the index on b_id

If I change the LIMIT from 1 to 3 pg starts using the index on b_id.

Already tried running REINDEX and VACUUM ANALYZE on both A and B.
Nothing changed.

Why does this happen?
Is there any way I can hint/force the optimizer to use b_id index?

Thanks

--
Marcio Ribeiro

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2015-10-10 15:45:36 Re: LIMIT 1 poor query plan
Previous Message Kevin Grittner 2015-10-09 13:01:53 Re: Re: Multi processor server overloads occationally with system process while running postgresql-9.4