Received: from makus.postgresql.org ([98.129.198.125]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TOHXU-00087E-TZ for pgsql-performance@postgresql.org; Wed, 17 Oct 2012 00:31:13 +0000 Received: from momjian.us ([72.94.173.45]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TOHXQ-0004fc-CW for pgsql-performance@postgresql.org; Wed, 17 Oct 2012 00:31:12 +0000 Received: from bruce by momjian.us with local (Exim 4.72) (envelope-from ) id 1TOHXO-0006kS-Pe; Tue, 16 Oct 2012 20:31:06 -0400 Date: Tue, 16 Oct 2012 20:31:06 -0400 From: Bruce Momjian To: Chris Ruprecht Cc: Evgeny Shishkin , "pgsql-performance@postgresql.org" Subject: Re: Have: Seq Scan - Want: Index Scan - what am I doing wrong? Message-ID: <20121017003106.GI7480@momjian.us> References: <91417DD6-C9FF-40EA-8EA9-87DF7E696927@cdrbill.com> <9BB26B73-2824-4C97-92B5-5E059108C071@gmail.com> <2A64CF51-CC89-4A0A-89CC-49D7ABFD4603@cdrbill.com> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <2A64CF51-CC89-4A0A-89CC-49D7ABFD4603@cdrbill.com> User-Agent: Mutt/1.5.20 (2009-06-14) X-Pg-Spam-Score: -2.3 (--) X-Archive-Number: 201210/222 X-Sequence-Number: 48181 On Tue, Oct 16, 2012 at 08:19:43PM -0400, Chris Ruprecht wrote: > > On Oct 16, 2012, at 20:01 , Evgeny Shishkin wrote: > > > Selecting 5 yours of data is not selective at all, so postgres decides it is cheaper to do seqscan. > > > > Do you have an index on patient.dnsortpersonnumber? Can you post a result from > > select count(*) from patient where dnsortpersonnumber = '347450'; ? > > > > Yes, there is an index: > > "Aggregate (cost=6427.06..6427.07 rows=1 width=0)" > " -> Index Scan using patient_pracsortpatientnumber on patient (cost=0.00..6427.06 rows=1 width=0)" > " Index Cond: (dnsortpersonnumber = '347450'::text)" > > > In fact, all the other criteria is picked using an index. I fear that the >= and <= on the timestamp is causing the issue. If I do a "=" of just one of them, I get an index scan. But I need to scan the entire range. I get queries like "give me everything that was entered into the system for this patient between these two dates". A single date wouldn't work. Have you read our FAQ on this matter? http://wiki.postgresql.org/wiki/FAQ#Why_are_my_queries_slow.3F_Why_don.27t_they_use_my_indexes.3F -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +