X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 0F0EF9FA22C for ; Fri, 19 May 2006 17:59:30 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 24734-05 for ; Fri, 19 May 2006 17:59:24 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from noel.decibel.org (noel.decibel.org [67.100.216.10]) by postgresql.org (Postfix) with ESMTP id C20799FA278 for ; Fri, 19 May 2006 17:59:22 -0300 (ADT) Received: by noel.decibel.org (Postfix, from userid 1001) id EBF6156427; Fri, 19 May 2006 15:59:19 -0500 (CDT) Received: (hashcash-sendmail, from uid 1001); Fri, 19 May 2006 15:59:16 -0500 Date: Fri, 19 May 2006 15:59:15 -0500 From: "Jim C. Nasby" To: Stephen Byers Cc: Tom Lane , "Steinar H. Gunderson" , pgsql-performance@postgresql.org Subject: Re: why is bitmap index chosen for this query? Message-ID: <20060519205915.GO64371@pervasive.com> References: <16019.1147973659@sss.pgh.pa.us> <20060518193818.81332.qmail@web30710.mail.mud.yahoo.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <20060518193818.81332.qmail@web30710.mail.mud.yahoo.com> X-Operating-System: FreeBSD 6.0-RELEASE-p4 amd64 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.11 X-Hashcash: 1:20:060519:stephenabyers@yahoo.com::qgCVF1jAQqb9ajBA:00000000000000 00000000000000000000000018+p X-Hashcash: 1:20:060519:tgl@sss.pgh.pa.us::4sEZNNqBjL5IujCi:00000000000000000000 0000000000000000000000002iZ8 X-Hashcash: 1:20:060519:sgunderson@bigfoot.com::NycGZcOaO1b8M1SO:000000000000000 000000000000000000000000AJr4 X-Hashcash: 1:20:060519:pgsql-performance@postgresql.org::ivAhQ2xWxIlY8g+V:00000 000000000000000000000000DiLo X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200605/406 X-Sequence-Number: 19193 On Thu, May 18, 2006 at 12:38:18PM -0700, Stephen Byers wrote: > I repeated explain analyze on the query 5 times and it came up with the same plan. > > You asked about index order and physical table order. In general the index order is indeed close to the same order as the physical table order. However, this query is likely an exception. The data is actually from a backup server that has filled a hole for some of the time range that I'm specifying in my query. What's SELECT correlation FROM pg_stats WHERE tablename='packets' AND attname='environment_name' show? What's effective_cache_size and random_page_cost set to? Also, out of curiosity, why not just use a timestamp instead of two int's for storing time? > Wow -- so what does that mean? Do I need to leave my work_mem at 100MB?? I mentioned that my application actually uses a cursor to walk through this data. Even though the bitmap scan technically had the fastest time with explain analyze, it takes a long while (20 seconds) before the results start to come back through the cursor. Conversely, with the index scan, results immediately come back through the cursor method (which is more desirable). Thoughts? Do you really need to use a cursor? It's generally less efficient than doing things with a single SQL statement, depending on what exactly you're doing. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461