Received: from sss.pgh.pa.us ([192.204.191.242]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f83NVM760533 for ; Mon, 3 Sep 2001 19:31:22 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id f83NVIK11946; Mon, 3 Sep 2001 19:31:18 -0400 (EDT) To: kbussey@wisol.com cc: pgsql-sql@postgresql.org Subject: Re: Index Scan Backward vs. Sort/Sequential Scan when using ORDER BY In-reply-to: <01083012294300.06825@cronus.wisol.net> References: <01083012294300.06825@cronus.wisol.net> Comments: In-reply-to Keith Bussey message dated "Thu, 30 Aug 2001 12:38:48 -0400" Date: Mon, 03 Sep 2001 19:31:18 -0400 Message-ID: <11943.999559878@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200109/12 X-Sequence-Number: 4658 Keith Bussey writes: > In trying to figure out just why my ORDER BY queries were so slow, I came > across something interesting. The issue here seems to be that Postgres is drastically underestimating the number of rows that will come out of the indexscan in the second case: > -> Index Scan using index_client_profiles_gender on > client_profiles p (cost=0.00..35064.98 rows=198 width=8) 198 rows out when you have 54713 females seems a tad low; if it is indeed much too low, that would explain why the planner mistakenly prefers this plan. It'd be interesting to look at the EXPLAIN estimate and actual results for SELECT count(*) FROM client_profiles p WHERE (p.profiles_gender='F'); SELECT count(*) FROM client_profiles p WHERE (p.profiles_gender='F') AND (p.profiles_orientation[2] = 'F' OR p.profiles_orientation[1]='M'); I suspect the main problem may be lack of stats about the array element distributions. Does profiles_orientation really need to be an array, or could you break it out into separate fields? regards, tom lane