The following bug has been logged on the website: Bug reference: 7629 Logged by: Daniel Migowski Email address: dmigow...@ikoffice.de PostgreSQL version: 9.1.2 Operating system: Debian Linux 5.0 Description:
Hi! PostgreSQL chooses to seq scan a table when I give an additional sort operator, and I don't know why. This is the small example: I have a table "delivery". There is a functional, partial index on the table I'd like to use for querying: CREATE INDEX idx_delivery_naturalnumber ON delivery(prep_natural_sort(number)) WHERE recordstatus < 'X'; The field "number" is not null, btw. Then I do a query select d.id, d.number from Delivery d where d.recordStatus<'X' and (d.tenant_id=1 or d.tenant_id=2) order by prep_natural_sort(d.number) ASC limit 1 The where-clause matches the partial index, and the order by clause matches the index fields, so no problem here. "Limit (cost=0.00..0.34 rows=1 width=12)" " Output: id, number, (prep_natural_sort((number)::text))" " -> Index Scan using idx_delivery_naturalnumber on public.delivery d (cost=0.00..46655.30 rows=135680 width=12)" " Output: id, number, prep_natural_sort((number)::text)" " Filter: ((d.tenant_id = 1) OR (d.tenant_id = 2))" However, when I also want to order by id: select d.id, d.number from Delivery d where d.recordStatus<'X' and (d.tenant_id=1 or d.tenant_id=2) order by prep_natural_sort(d.number) ASC, d.id ASC limit 1 it does a sequential scan. "Limit (cost=43443.23..43443.24 rows=1 width=12)" " Output: id, number, (prep_natural_sort((number)::text))" " -> Sort (cost=43443.23..43782.43 rows=135680 width=12)" " Output: id, number, (prep_natural_sort((number)::text))" " Sort Key: (prep_natural_sort((d.number)::text)), d.id" " -> Seq Scan on public.delivery d (cost=0.00..42764.83 rows=135680 width=12)" " Output: id, number, prep_natural_sort((number)::text)" " Filter: ((d.recordstatus < 'X'::recordstatus) AND ((d.tenant_id = 1) OR (d.tenant_id = 2)))" This is a bit stupid, also because the relevant data could be fetched very fast by the first order-by expression, and then the results could be ordered again, which is then much faster than doing a full sequential scan on the data. Do you think its worth to optimize for this case? What are the problems? If the index scan is considered fast in the first case, why not do it also in the second case? The only difference would be that one had to fetch a few more rows in the index scan potentially, because the secondary sort would need to impose an order on the first rows returned from the index. select VERSION(); "PostgreSQL 9.1.2 on i686-pc-linux-gnu, compiled by gcc (Debian 4.3.2-1.1) 4.3.2, 32-bit" -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs