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

Re: SQL With Dates

From: "Mark Lewis" <mark(dot)lewis(at)mir3(dot)com>
To: "Rafael Domiciano" <rafael(dot)domiciano(at)gmail(dot)com>
Cc: <pgsql-performance(at)postgresql(dot)org>
Subject: Re: SQL With Dates
Date: 2009-04-20 20:04:47
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
It sounds like what you're doing is comparing the planner's cost
estimate from running EXPLAIN on a few different queries.  The planner's
cost estimate was never intended to do what you're trying to do; it's
not an absolute scale of cost, it's just a tool that the planner uses to
get relative comparisons of logically equivalent plans.

The actual number that the planner spits out is meaningless in an
absolute sense.  It's entirely possible that one query with an estimated
cost of 10000 will run faster than a query with an estimated cost of
100.  What you actually need to do is compare the real running time of
the queries in order to see which ones are actually problematic.

For that, you'd do better using a tool like pgFouine to look at actual
performance trends. 

-- Mark

On Mon, 2009-04-20 at 10:55 -0300, Rafael Domiciano wrote:
> Hello People,
> I have initiated a work to review the sqls of our internal software.
> Lot of them he problem are about sql logic, or join with table
> unecessary, and so on.
> But software has lot of sql with date, doing thinks like:
> [..]
>   date >= '2009-04-01' AND
>   date <= '2009-04-15'
> [..]
> Redoing the SQL with fix date (date = '2009-04-01') o cost in explain
> always still about 200 or less. But with a period the cost is high,
> about 6000 or more.
> Select is using Index and the date is using index too.
> There is some way to use date period with less cost?
> Rafael Domiciano

In response to

pgsql-performance by date

Next:From: davidDate: 2009-04-20 21:53:21
Subject: performance for high-volume log insertion
Previous:From: Scott MarloweDate: 2009-04-20 18:59:12
Subject: Re: SQL With Dates

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