From: Nowak Michał <michal.nowak@me.com>
To: pgsql-performance@postgresql.org
Subject: Re: Query with order by and limit is very slow - wrong index used
Date: Tue, 04 Oct 2011 10:55:18 +0200
Message-ID: <B01E1981-8B81-437D-940E-00AD31ADCDCE@me.com> (raw)
In-Reply-To: <CAJY59_jMAx9U07Xfk_iuEdKF6s6n8CR7baaWvzUYPrYTznUM3A@mail.gmail.com>
References: <6B8FADC8-9692-439F-8AA4-0CAA287FCEB0@me.com>
<15615.1317654751@sss.pgh.pa.us>
<E3DE92CA-D03A-4030-9338-6C523502777A@me.com>
<CAJY59_jMAx9U07Xfk_iuEdKF6s6n8CR7baaWvzUYPrYTznUM3A@mail.gmail.com>
Lowering random_page_cost didn't help -- I've tried values 2.0 and 1.5.
Then I tried "order by id -1" hack Marcin Mańk proposed...
a9-dev=> create index foo on records(source_id, (id - 1));
CREATE INDEX
a9-dev=> explain analyze select * from records where source_id ='http://www.wbc.poznan.pl/dlibra/oai-pmh-repository.xml'; order by (id -1) limit 200;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..379.42 rows=200 width=1124) (actual time=0.137..255.283 rows=200 loops=1)
-> Index Scan using foo on records (cost=0.00..1864617.14 rows=982887 width=1124) (actual time=0.137..255.237 rows=200 loops=1)
Index Cond: ((source_id)::text = 'http://www.wbc.poznan.pl/dlibra/oai-pmh-repository.xml'::text)
Total runtime: 255.347 ms
(4 rows)
Significant improvement :)
As we can see, it is possible to query records fast without changing table structure. Question is: can I do it without "hacks"?
Michal Nowak
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: michal.nowak@me.com
Subject: Re: Query with order by and limit is very slow - wrong index used
In-Reply-To: <B01E1981-8B81-437D-940E-00AD31ADCDCE@me.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox