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
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 |