Re: query plan - Mailing list pgsql-bugs

ag20 wrote:

> Is there a reason that the expressions:
>
>  Crops.change_e > '10/1/2001'
>
>  '10/1/2001' < Crops.change_e
>
> when used in a WHERE clause of a query should yield
> a good plan for the first and a bad plan for the second?

Disclaimer: I'm tired and haven't tested this.

This "problem" usually occurs when the constant and the column is not of the same data type.  In the first instance the
queryplanner does a (single) conversion of the constant, in the second a conversion of every single row. 

Try

    TIMESTAMT WITH TIME ZONE '10/1/2001' < Crops.change_e

Allan.

>
>
> They both yield the same boolean value.
>
> The attribute Crops.change_e is not involved in any index.
>
> The query with the first form of the expression took under 1 second to run.
> It used the "crops_loct" index.
> There are a lot of "loct" with only a few rows for each in crops.
>
> The query with the second form of the expression took aprox. 20 seconds to
> run.
> It used the "crops_commtype" index.
> There are only a few "commtype" with a lot of rows for each in crops.
>
> The table crops has
> 85454 rows,
> 16594 distinct loct and
> 199 distinct commtype.
>
> This was run on psql, postgres 7.1.3, slackware 8.0 (kernel 2.2.19), 133Mhz
> i86.
>
>                             version
> ---------------------------------------------------------------
>  PostgreSQL 7.1.3 on i586-pc-linux-gnu, compiled by GCC 2.95.3
>
> Following are:
>  The two sql statements
>  The results of VACUUM VERBOSE ANALYZE of the involved tables
>  The results of their EXPLAINs
>  The structures of the tables and their indexes.
>
> Attached is runbug.sql which will create the tables and indexes,
> insert 75 rows in the crops table,
> insert 13 rows in the commtypes table,
> 17 rows in the plantunits table,
> vacuum the three tables and
> run the 2 explains.
> This resulted in the same index switch as with the larger tables
> I am showing here.
>
> Here is the EXPLAIN with Crops.change_e > '10/1/2001'.
>
> EXPLAIN
> SELECT
>  Crops.number,
>  Crops.change_s,
>  Commtypes.name,
>  Crops.pseq,
>  Crops.quantity,
>  PlantUnits.id
> FROM
>  Crops,
>  Commtypes,
>  PlantUnits
> WHERE
>  Crops.Loct = 757277953 AND
>  Crops.Commtype = Commtypes.number AND
>  Crops.PlantUnit = PlantUnits.number AND
>  Crops.change_e > '10/1/2001'
> ;
>
> Here is the EXPLAIN with '10/1/2001' < Crops.change_e.
>
> EXPLAIN
> SELECT
>  Crops.number,
>  Crops.change_s,
>  Commtypes.name,
>  Crops.pseq,
>  Crops.quantity,
>  PlantUnits.id
> FROM
>  Crops,
>  Commtypes,
>  PlantUnits
> WHERE
>  Crops.Loct = 757277953 AND
>  Crops.Commtype = Commtypes.number AND
>  Crops.PlantUnit = PlantUnits.number AND
>  '10/1/2001' < Crops.change_e
> ;
>
> Vacuum tables involved in the queries
>
> NOTICE:  --Relation crops--
> NOTICE:  Pages 1055: Changed 0, reaped 0, Empty 0, New 0; Tup 85454: Vac 0,
> Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 96, MaxLen 96; Re-using: Free/Avail.
> Space 0/0; EndEmpty/Avail. Pages 0/0. CPU 0.26s/0.16u sec.
> NOTICE:  Index crops_number: Pages 718; Tuples 85454. CPU 0.39s/1.31u sec.
> NOTICE:  Index crops_commtype: Pages 259; Tuples 85454. CPU 0.18s/0.81u sec.
> NOTICE:  Index crops_loct: Pages 246; Tuples 85454. CPU 0.10s/0.64u sec.
> NOTICE:  Analyzing...
>
> NOTICE:  --Relation commtypes--
> NOTICE:  Pages 6: Changed 0, reaped 0, Empty 0, New 0; Tup 508: Vac 0,
> Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 83, MaxLen 95; Re-using: Free/Avail.
> Space 0/0; EndEmpty/Avail. Pages 0/0. CPU 0.00s/0.00u sec.
> NOTICE:  Index commtypes_number: Pages 6; Tuples 508. CPU 0.01s/0.00u sec.
> NOTICE:  Index commtypes_id: Pages 2; Tuples 508. CPU 0.00s/0.00u sec.
> NOTICE:  Index commtypes_name: Pages 4; Tuples 508. CPU 0.00s/0.00u sec.
> NOTICE:  Analyzing...
>
> NOTICE:  --Relation plantunits--
> NOTICE:  Pages 1: Changed 0, reaped 0, Empty 0, New 0; Tup 17: Vac 0,
> Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 85, MaxLen 90; Re-using: Free/Avail.
> Space 0/0; EndEmpty/Avail. Pages 0/0. CPU 0.00s/0.00u sec.
> NOTICE:  Index plantunits_number: Pages 2; Tuples 17. CPU 0.01s/0.00u sec.
> NOTICE:  Index plantunits_teal: Pages 2; Tuples 17. CPU 0.00s/0.00u sec.
> NOTICE:  Index plantunits_id: Pages 2; Tuples 17. CPU 0.00s/0.00u sec.
> NOTICE:  Analyzing...
>
> Here are the results of the EXPLAIN with Crops.change_e > '10/1/2001' .
>
> NOTICE:  QUERY PLAN:
>
> Nested Loop  (cost=11.01..17.75 rows=3 width=64)
>   ->  Merge Join  (cost=11.01..11.27 rows=3 width=48)
>         ->  Sort  (cost=9.50..9.50 rows=3 width=32)
>               ->  Index Scan using crops_loct on crops  (cost=0.00..9.47
> rows=3 width=32)
>         ->  Sort  (cost=1.52..1.52 rows=17 width=16)
>               ->  Seq Scan on plantunits  (cost=0.00..1.17 rows=17 width=16)
>   ->  Index Scan using commtypes_number on commtypes  (cost=0.00..2.01
> rows=1 width=16)
>
> Here are the results of the EXPLAIN with '10/1/2001' < Crops.change_e.
>
> NOTICE:  QUERY PLAN:
>
> Nested Loop  (cost=0.00..nan rows=nan width=64)
>   ->  Nested Loop  (cost=0.00..nan rows=nan width=48)
>         ->  Index Scan using crops_commtype on crops  (cost=0.00..11411.10
> rows=nan width=32)
>         ->  Seq Scan on plantunits  (cost=0.00..1.17 rows=17 width=16)
>   ->  Seq Scan on commtypes  (cost=0.00..11.08 rows=508 width=16)
>
> Here are the structures of the files and the indexes.
>
>                   Table "crops"
>  Attribute |           Type           | Modifier
> -----------+--------------------------+----------
>  number    | integer                  |
>  change_s  | timestamp with time zone |
>  change_e  | timestamp with time zone |
>  active    | boolean                  |
>  edit_s    | timestamp with time zone |
>  edit_e    | timestamp with time zone |
>  loct      | integer                  |
>  commtype  | integer                  |
>  pseq      | integer                  |
>  quantity  | double precision         |
>  plantunit | integer                  |
> Indices: crops_commtype,
>          crops_loct,
>          crops_number
>
> Index "crops_commtype"
>  Attribute |  Type
> -----------+---------
>  commtype  | integer
> btree
>
>  Index "crops_loct"
>  Attribute |  Type
> -----------+---------
>  loct      | integer
> btree
>
>          Index "crops_number"
>  Attribute |           Type
> -----------+--------------------------
>  number    | integer
>  change_s  | timestamp with time zone
>  edit_s    | timestamp with time zone
> unique btree
>
>                 Table "commtypes"
>  Attribute |           Type           | Modifier
> -----------+--------------------------+----------
>  number    | integer                  |
>  change_s  | timestamp with time zone |
>  change_e  | timestamp with time zone |
>  active    | boolean                  |
>  edit_s    | timestamp with time zone |
>  edit_e    | timestamp with time zone |
>  id        | integer                  |
>  name      | character varying(20)    |
> Indices: commtypes_id,
>          commtypes_name,
>          commtypes_number
>
> Index "commtypes_id"
>  Attribute |  Type
> -----------+---------
>  id        | integer
> btree
>
>       Index "commtypes_name"
>  Attribute |         Type
> -----------+-----------------------
>  name      | character varying(20)
> btree
>
>        Index "commtypes_number"
>  Attribute |           Type
> -----------+--------------------------
>  number    | integer
>  change_s  | timestamp with time zone
>  edit_s    | timestamp with time zone
> unique btree
>
>                Table "plantunits"
>  Attribute |           Type           | Modifier
> -----------+--------------------------+----------
>  number    | integer                  |
>  change_s  | timestamp with time zone |
>  change_e  | timestamp with time zone |
>  active    | boolean                  |
>  edit_s    | timestamp with time zone |
>  edit_e    | timestamp with time zone |
>  id        | character varying(5)     |
>  teal      | character varying(2)     |
> Indices: plantunits_id,
>          plantunits_number,
>          plantunits_teal
>
>       Index "plantunits_id"
>  Attribute |         Type
> -----------+----------------------
>  id        | character varying(5)
> btree
>
>       Index "plantunits_number"
>  Attribute |           Type
> -----------+--------------------------
>  number    | integer
>  change_s  | timestamp with time zone
>  edit_s    | timestamp with time zone
> unique btree
>
>      Index "plantunits_teal"
>  Attribute |         Type
> -----------+----------------------
>  teal      | character varying(2)
> btree
>
>                  Name: runbug.sql
>    runbug.sql    Type: unspecified type (application/octet-stream)
>              Encoding: x-uuencode