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

slow plan for min/max

From: Pailloncy Jean-Gérard <pailloncy(at)ifrance(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: slow plan for min/max
Date: 2003-09-07 18:04:20
Message-ID: B451DA6A-E15D-11D7-B435-00039394E6D8@ifrance.com (view raw or flat)
Thread:
Lists: pgsql-performance
I have:
> psql (PostgreSQL) 7.3.2
I do a modification of 'access/index/indexam.c' where I comment:
> #ifdef NOT_USED
>         if (scan->keys_are_unique && scan->got_tuple)
>         {
>                 if (ScanDirectionIsForward(direction))
>                 {
>                         if (scan->unique_tuple_pos <= 0)
>                                 scan->unique_tuple_pos++;
>                 }
>                 else if (ScanDirectionIsBackward(direction))
>                 {
>                         if (scan->unique_tuple_pos >= 0)
>                                 scan->unique_tuple_pos--;
>                 }
>                 if (scan->unique_tuple_pos == 0)
>                         return heapTuple;
>                 else
>                         return NULL;
>         }
> #endif
I do not remember the references of the bug.
But the solution was planned for 7.4.

I do:
> psql=# \di
> [skip]
>  public | url_next_index_time      | index | postgresql | url
>  [skip]
> (11 rows)
I have an index on next_index_time field on table url.

> psql=# explain select min(next_index_time) from url \g
>                             QUERY PLAN
> -------------------------------------------------------------------
>  Aggregate  (cost=85157.70..85157.70 rows=1 width=4)
>    ->  Seq Scan on url  (cost=0.00..80975.56 rows=1672856 width=4)
> (2 rows)
Silly SeqScan of all the table.

> psql=# explain SELECT next_index_time FROM url ORDER BY  
> next_index_time LIMIT 1 \g
>                                            QUERY PLAN
> ----------------------------------------------------------------------- 
> -------------------------
>  Limit  (cost=0.00..0.20 rows=1 width=4)
>    ->  Index Scan using url_next_index_time on url   
> (cost=0.00..340431.47 rows=1672856 width=4)
> (2 rows)
I ask for the same thing.
That's better !

Why the planner does that ?

Jean-Gérard Pailloncy
Paris, France


Responses

pgsql-performance by date

Next:From: Greg StarkDate: 2003-09-07 23:18:01
Subject: Re: [GENERAL] how to get accurate values in pg_statistic
Previous:From: Bruce MomjianDate: 2003-09-07 16:22:55
Subject: Re: [GENERAL] how to get accurate values in pg_statistic

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