Thread: SQL With Dates
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
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
BETWEEN X AND Y On Mon, Apr 20, 2009 at 2:55 PM, Rafael Domiciano <rafael.domiciano@gmail.com> 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 > -- GJ
Hello Grzegorz,
Thnks for response, but lot of the selects is using BETWEEN and the cost is the same.
Thnks for response, but lot of the selects is using BETWEEN and the cost is the same.
2009/4/20 Grzegorz Jaśkiewicz <gryzman@gmail.com>
BETWEEN X AND Y--
On Mon, Apr 20, 2009 at 2:55 PM, Rafael Domiciano
<rafael.domiciano@gmail.com> 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
>
GJ
On Mon, Apr 20, 2009 at 7:55 AM, Rafael Domiciano <rafael.domiciano@gmail.com> 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. Yep. Because you'll be getting more rows. More rows == more cost. TANSTAAFL.
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
On Mon, Apr 20, 2009 at 9:55 AM, Rafael Domiciano <rafael.domiciano@gmail.com> 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? If you have an actual performance problem (as opposed to a big number in EXPLAIN), then it's possible that the planner isn't estimating the number of rows that will be in that range very accurately. In that case, you might need to increase the statistics target for that column, or your default_statistics_target. In 8.3, the default default_statistics_target = 10. In 8.4, it will be 100, so you might try that for a starting point. But date columns can sometimes have highly skewed data, so you might find that you need an even higher value for that particular column. I wouldn't recommend raising the database-wide setting above 100 though (though I know some people have used 200 or 400 without too much pain, especially on Really Big Databases where longer planning time isn't a big deal because the execution times are measured in minutes - it doesn't sound like that's your situation though). The first thing, to do, is see how fast the query actually runs. Try setting \timing in psql and running the query to see how long it actually takes. If it's fast enough, you're done. If not, run EXPLAIN ANALYZE and compare the estimated row counts to t he actual row counts. If they're pretty close, you're out of luck - as others have already said, TANSTAAFL. If they're way off, the try the above. ...Robert