FAQ
Hello,

I just installed PG 7.0 and did some testing. I found what I believe is
a strange behaviour:

xtnet=# \d telecom
Table "telecom"
Attribute | Type | Modifier
-----------+-------------+----------
regione | varchar(20) |
distretto | varchar(20) |
settore | varchar(20) |
descriz | varchar(60) |
cor_x | float8 |
cor_y | float8 |
cod_dis | varchar(4) |
rete_urb | varchar(24) |

xtnet=# create index telecom_regione on telecom ( regione );
CREATE

xtnet=# explain select distinct on (regione) * from telecom;
NOTICE: QUERY PLAN:

Unique (cost=0.00..4777.43 rows=3910 width=88)
-> Index Scan using telecom_regione on telecom (cost=0.00..4679.68
rows=39100 width=88)

EXPLAIN

xtnet=# explain select distinct on (regione) regione from telecom;
NOTICE: QUERY PLAN:

Unique (cost=4370.91..4468.66 rows=3910 width=12)
-> Sort (cost=4370.91..4370.91 rows=39100 width=12)
-> Seq Scan on telecom (cost=0.00..1079.00 rows=39100
width=12)

EXPLAIN

xtnet=# explain select distinct on (regione) distretto,regione from
telecom;
NOTICE: QUERY PLAN:

Unique (cost=4492.72..4590.47 rows=3910 width=24)
-> Sort (cost=4492.72..4492.72 rows=39100 width=24)
-> Seq Scan on telecom (cost=0.00..1079.00 rows=39100
width=24)

EXPLAIN

It seems like the index is used only if * appears in the target list.
???

Bye!

--
Daniele

-------------------------------------------------------------------------------
Daniele Orlandi - Utility Line Italia - http://www.orlandi.com
Via Mezzera 29/A - 20030 - Seveso (MI) - Italy
-------------------------------------------------------------------------------

Search Discussions

  • Tom Lane at May 10, 2000 at 6:19 am

    Daniele Orlandi writes:
    [ example snipped ]
    It seems like the index is used only if * appears in the target list.
    There is certainly not a dependence on * as such. However, the
    estimated row width does affect the cost estimate for operations like
    SORT, where we have to guess how many rows will fit in memory. It looks
    to me like your example case is right near the boundary where the system
    thinks that index scan and sort are of roughly equal cost, so relatively
    small changes will push the choice in one direction or the other.
    This should not be taken as an indication that someone actually
    considered the specific examples and decided it should act that way!
    It just falls out of the behavior of the cost-estimate-driven planner
    structure. Ideally, the hope is not that this sort of choice is
    perfectly right all the time; my ambition only extends to the hope that
    if the system thinks index scan and sort are of roughly equal cost,
    then indeed they are, and so it wouldn't matter a whole lot which one
    gets picked.

    Of course we aren't all that close to meeting the ideal goal :-(.
    I'm assuming that you are complaining because one or the other of
    these plans is actually much cheaper than the other in your example.
    You have, however, carefully refrained from giving us any hint which.
    Care to fess up with more details?

    regards, tom lane
  • Daniele Orlandi at May 11, 2000 at 1:08 am

    Tom Lane wrote:

    There is certainly not a dependence on * as such.
    Yes, that was pretty strange, but, not knowing the internals of the
    optimizer, I wondered why a change in the target list could have changed
    the decision of the optimizer.
    However, the estimated row width does affect the cost estimate for operations
    like SORT, where we have to guess how many rows will fit in memory. It looks
    to me like your example case is right near the boundary where the system
    thinks that index scan and sort are of roughly equal cost, so relatively
    small changes will push the choice in one direction or the other.
    Yes this is what's happening. I progressively adding attributes to the
    targets list and, at some point, the optimizer choosed the other
    alternative.
    I'm assuming that you are complaining because one or the other of
    these plans is actually much cheaper than the other in your example.
    Yes, more than 12:1 ratio.
    You have, however, carefully refrained from giving us any hint which.
    Care to fess up with more details?
    Yes, of course, I hope the following statistics can help you :

    This one is the result of vacuum on the table:

    NOTICE: Pages 688: Changed 0, reaped 0, Empty 0, New 0; Tup 39100: Vac
    0, Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 100, MaxLen 201; Re-using:
    Free/Avail. Space 0/0; EndEmpty/Avail. Pages 0/0. CPU 0.04s/3.37u sec.
    NOTICE: Index telecom_settore: Pages 145; Tuples 39100. CPU 0.01s/0.07u
    sec.
    NOTICE: Index telecom_distretto: Pages 156; Tuples 39100. CPU
    0.01s/0.06u sec.
    NOTICE: Index telecom_regione: Pages 136; Tuples 39100. CPU 0.01s/0.06u
    sec.

    This one is with the index:
    ! system usage stats:
    ! 0.909758 elapsed 0.830000 user 0.050000 system sec
    ! [0.870000 user 0.060000 sys total]
    ! 0/0 [0/0] filesystem blocks in/out
    ! 282/14 [429/279] page faults/reclaims, 0 [0] swaps
    ! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
    ! 0/0 [0/0] voluntary/involuntary context switches
    ! postgres usage stats:
    ! Shared blocks: 845 read, 0 written, buffer hit rate =
    97.86%
    ! Local blocks: 0 read, 0 written, buffer hit rate =
    0.00%
    ! Direct blocks: 0 read, 0 written

    This one is without the index:
    ! system usage stats:
    ! 12.529637 elapsed 12.360000 user 0.150000 system sec
    ! [12.380000 user 0.190000 sys total]
    ! 0/0 [0/0] filesystem blocks in/out
    ! 508/360 [695/657] page faults/reclaims, 0 [0] swaps
    ! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
    ! 0/0 [0/0] voluntary/involuntary context switches
    ! postgres usage stats:
    ! Shared blocks: 688 read, 1 written, buffer hit rate =
    0.72%
    ! Local blocks: 0 read, 0 written, buffer hit rate =
    0.00%
    ! Direct blocks: 0 read, 0 written

    If you need other statistics/tests, just ask and I will be happy to help
    you.

    Bye!

    --
    Daniele

    -------------------------------------------------------------------------------
    Daniele Orlandi - Utility Line Italia - http://www.orlandi.com
    Via Mezzera 29/A - 20030 - Seveso (MI) - Italy
    -------------------------------------------------------------------------------

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedMay 10, '00 at 3:06a
activeMay 11, '00 at 1:08a
posts3
users2
websitepostgresql.org...
irc#postgresql

2 users in discussion

Daniele Orlandi: 2 posts Tom Lane: 1 post

People

Translate

site design / logo © 2021 Grokbase