Re: query plan

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 query planner 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