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

Re: Pet Peeves?

From: Greg Smith <gsmith(at)gregsmith(dot)com>
To: Gregory Stark <stark(at)enterprisedb(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Pet Peeves?
Date: 2009-01-29 19:12:45
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-general
The biggest peeve I still have to fight is attached to the old "why aren't 
there any optimizer hints?" tree.  PostgreSQL forces you to understand a 
non-trivial amount of how the query optimizer works before you can get it 
to do the right thing once you get beyond a small database, and nobody 
likes doing the "why isn't it using the index?!" dance.

When you turn enable_seqscan off and it proceeds to do a seqscan anyway 
when there's seemingly a perfect index right there, it's pretty 
frustrating.  I spent the better part of a weekend last year fighting a 
battle with a single "select * from t where type='x' and ts>='date1' and 
ts<='date2", running against a giant table with an index on (type,ts) that 
just stopped using that index for mysterious reasons.  You do not want to 
seqscan a billion rows.

The main thing I'd like to see here is more visibility into rejected 
plans, so that at least people could see those costs.  If I could have 
seen whether the cost estimate on the index-based plan was going up or 
down as I tweaked parameters/updated stats, at least then I could quantify 
my progress toward returning to where the right plan was preferred one. 
The flip side is that as it is right now, it's also hard to answer the 
question "how close am I to having this plan fail?" until it already has. 
I know there's been some academic work in this area as part of classes on 
database internals, I'd like to see some of that turn into a production 

* Greg Smith gsmith(at)gregsmith(dot)com Baltimore, MD

In response to

  • Pet Peeves? at 2009-01-29 13:16:17 from Gregory Stark

pgsql-general by date

Next:From: Michael MonnerieDate: 2009-01-29 19:14:28
Subject: Re: [GENERAL] Encoding problem using pg_dumpall
Previous:From: Tom LaneDate: 2009-01-29 19:11:37
Subject: Re: Changing owner of pg_toast tables

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