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

Re: query planner weirdness?

From: "Adam Rich" <adam(dot)r(at)sbcglobal(dot)net>
To: "'Tom Lane'" <tgl(at)sss(dot)pgh(dot)pa(dot)us>,"'Bob Duffey'" <bobduffey68(at)gmail(dot)com>
Cc: <pgsql-general(at)postgresql(dot)org>
Subject: Re: query planner weirdness?
Date: 2008-06-28 04:53:00
Message-ID: 06b201c8d8da$d8111f10$88335d30$@r@sbcglobal.net (view raw or flat)
Thread:
Lists: pgsql-general
> 
> "Bob Duffey" <bobduffey68(at)gmail(dot)com> writes:
> > I'm seeing some query plans that I'm not expecting.  The table in
> question
> > is reasonably big (130,000,000 rows).  The table has a primary key,
> indexed
> > by one field ("ID", of type bigint).  Thus, I would expect the
> following
> > query to simply scan through the table using the primary key:
> 
> > select * from "T" order by "ID"
> 
> This is not wrong, or at least not obviously wrong.  A full-table
> indexscan is often slower than seqscan-and-sort.  If the particular
> case is wrong for you, you need to look at adjusting the planner's
> cost parameters to match your environment.  But you didn't provide any
> evidence that the chosen plan is actually worse than the alternative
> ...

I think I understand what Bob's getting at when he mentions blocking.  
The seqscan-and-sort would return the last record faster, but the 
indexscan returns the first record faster.  If you're iterating
through the records via a cursor, the indexscan behavior would be
more desirable.  You could get the initial rows back without waiting
for all 130 million to be fetched and sorted. 

In oracle, there is a first-rows vs. all-rows query hint for this sort
of thing.  









In response to

Responses

pgsql-general by date

Next:From: Bob DuffeyDate: 2008-06-28 05:00:43
Subject: Re: query planner weirdness?
Previous:From: Bob DuffeyDate: 2008-06-28 04:48:28
Subject: Re: query planner weirdness?

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