Skip site navigation (1) Skip section navigation (2)

Re: why is bitmap index chosen for this query?

From: "Jim C(dot) Nasby" <jnasby(at)pervasive(dot)com>
To: Stephen Byers <stephenabyers(at)yahoo(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>,"Steinar H(dot) Gunderson" <sgunderson(at)bigfoot(dot)com>,pgsql-performance(at)postgresql(dot)org
Subject: Re: why is bitmap index chosen for this query?
Date: 2006-05-19 20:59:15
Message-ID: 20060519205915.GO64371@pervasive.com (view raw or flat)
Thread:
Lists: pgsql-performance
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(at)pervasive(dot)com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

In response to

pgsql-performance by date

Next:From: CstdenisDate: 2006-05-19 22:56:49
Subject: How can I make this query faster (resend)
Previous:From: Jim C. NasbyDate: 2006-05-19 20:50:14
Subject: Re: Performance/Maintenance test result collection

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group