Re: Query planner question - Mailing list pgsql-general

At 03:46 13.06.2003, Dima Tkach said:
--------------------[snip]--------------------
>Hmmm... this seems to be something different from what it we started
>with...
>Where are all those indexes with the owid in the front?
>And also the one on (dcid, dsid, drid, nxid) isn't there either...

Right - I was using PGExplorer to list the table structure, and this
doesn't really seem to do a good job (simply mixed the column sequences
around)... I also had to manually add the constraints for the partial
indices and missed the "noid" constraint as well...

FWIW Here's the psql \d output:

test=# \d rv2_mdata \d id_mdata_dictid \d id_mdata_dictid_dec \d
id_mdata_dictid_int \d id_mdata_dictid_real \d id_mdata_dictid_string \d
id_mdata_dictid_timestamp \d id_mdata_dowid \d id_mdata_dpid \d
id_mdata_ioid \d id_mdata_owid \d id_mdata_pid \d rv2_mdata_pkey
                                   Table "rv2_mdata"
 Column |           Type           |                     Modifiers

--------+--------------------------+----------------------------------------
------------
 rid    | integer                  | not null default
nextval('rv2_mdata_id_seq'::text)
 pid    | integer                  |
 owid   | integer                  |
 ioid   | integer                  |
 dcid   | character varying        |
 dsid   | character varying        |
 drid   | integer                  |
 usg    | integer                  |
 idx    | character varying        |
 env    | integer                  |
 nxid   | integer                  |
 ci     | integer                  |
 cd     | numeric(21,6)            |
 cr     | real                     |
 cts    | timestamp with time zone |
 cst    | character varying        |
 ctx    | text                     |
 cbl    | oid                      |
 acl    | text                     |
Indexes: id_mdata_dictid,
         id_mdata_dictid_dec,
         id_mdata_dictid_int,
         id_mdata_dictid_real,
         id_mdata_dictid_string,
         id_mdata_dictid_timestamp,
         id_mdata_dowid,
         id_mdata_dpid,
         id_mdata_ioid,
         id_mdata_owid,
         id_mdata_pid
Primary key: rv2_mdata_pkey

  Index "id_mdata_dictid"
 Column |       Type
--------+-------------------
 dcid   | character varying
 dsid   | character varying
 drid   | integer
 nxid   | integer
btree

Index "id_mdata_dictid_dec"
 Column |       Type
--------+-------------------
 dcid   | character varying
 dsid   | character varying
 drid   | integer
 nxid   | integer
 cd     | numeric(21,6)
btree
Index predicate: ((usg & 2) = 2)

Index "id_mdata_dictid_int"
 Column |       Type
--------+-------------------
 dcid   | character varying
 dsid   | character varying
 drid   | integer
 nxid   | integer
 ci     | integer
btree
Index predicate: ((usg & 1) = 1)

Index "id_mdata_dictid_real"
 Column |       Type
--------+-------------------
 dcid   | character varying
 dsid   | character varying
 drid   | integer
 nxid   | integer
 cr     | real
btree
Index predicate: ((usg & 4) = 4)

Index "id_mdata_dictid_string"
 Column |       Type
--------+-------------------
 dcid   | character varying
 dsid   | character varying
 drid   | integer
 nxid   | integer
 cst    | character varying
btree
Index predicate: ((usg & 16) = 16)

 Index "id_mdata_dictid_timestamp"
 Column |           Type
--------+--------------------------
 dcid   | character varying
 dsid   | character varying
 drid   | integer
 nxid   | integer
 cts    | timestamp with time zone
btree
Index predicate: ((usg & 8) = 8)

   Index "id_mdata_dowid"
 Column |       Type
--------+-------------------
 owid   | integer
 dcid   | character varying
 dsid   | character varying
 drid   | integer
 nxid   | integer
 cd     | numeric(21,6)
btree
Index predicate: ((usg & 2) = 2)

Index "id_mdata_dictid_int"
 Column |       Type
--------+-------------------
 dcid   | character varying
 dsid   | character varying
 drid   | integer
 nxid   | integer
 ci     | integer
btree
Index predicate: ((usg & 1) = 1)

Index "id_mdata_dictid_real"
 Column |       Type
--------+-------------------
 dcid   | character varying
 dsid   | character varying
 drid   | integer
 nxid   | integer
 cr     | real
btree
Index predicate: ((usg & 4) = 4)

Index "id_mdata_dictid_string"
 Column |       Type
--------+-------------------
 dcid   | character varying
 dsid   | character varying
 drid   | integer
 nxid   | integer
 cst    | character varying
btree
Index predicate: ((usg & 16) = 16)

 Index "id_mdata_dictid_timestamp"
 Column |           Type
--------+--------------------------
 dcid   | character varying
 dsid   | character varying
 drid   | integer
 nxid   | integer
 cts    | timestamp with time zone
btree
Index predicate: ((usg & 8) = 8)

   Index "id_mdata_dowid"
 Column |       Type
--------+-------------------
 owid   | integer
 dcid   | character varying
 dsid   | character varying
 drid   | integer
 nxid   | integer
 usg    | integer
btree

   Index "id_mdata_dpid"
 Column |       Type
--------+-------------------
 pid    | integer
 dcid   | character varying
 dsid   | character varying
 drid   | integer
 nxid   | integer
 usg    | integer
btree

Index "id_mdata_ioid"
 Column |  Type
--------+---------
 ioid   | integer
 nxid   | integer
 usg    | integer
btree
Index predicate: (ioid IS NOT NULL)

Index "id_mdata_owid"
 Column |  Type
--------+---------
 owid   | integer
 nxid   | integer
 usg    | integer
btree

Index "id_mdata_pid"
 Column |  Type
--------+---------
 pid    | integer
 nxid   | integer
 usg    | integer
btree

Index "rv2_mdata_pkey"
 Column |  Type
--------+---------
 rid    | integer
unique btree (primary key)

>Actually, with *this* set on indices, I don't see anything that can be
>useful for your query at all - no wonder it goes for a seq scan :-)

right, I should stick to my SSH shell directly on the server ;-)

>BTW, you seem to have too many of them - perhaps, a little cleanup is in
>order...
>For example, the first two are totally unnecessary, because all the
>cases in which either of them could be useful are covered by
>"id_mdata_dowid" and  "id_mdata_dpid"

Now that I know that NULL is not indexable I will drop some of them - at
least the
key on ioid would never be used as I found. Since the predicate is "ioid IS
NOT NULL", any query giving "ioid = ###" will not match the predicate (at
least my old 7.2.1 doesn't use it...) I can also drop id_mdata_owid since
id_mdata_dowid should usable for such queries as well.

>Also, in general, it might make sense to look into the selectivity of
>all those combinations...
>For example, how many different "pid" and "owid" values is it reasonable
>to get among those rows for which all of "dcid","drid","dsid","nxid" are
>identical? If not very many, it might make sense to replace all four of
>"id_dictid_noid","id_mdata_dictid","id_mdata_dowid",id_mdata_dpid" with
>just one index on ("dcid","drid","dsid","nxid","usg")

There will be a lot of these combinations. For any given owid value there
will be up to 40 different dcid/dsid/drid combinations (owid being an
"owner record", and dcid/dsid/drid a pointer into a type dictionary). "pid"
is going to be dropped.

Thanks for your input :)


--
   >O     Ernest E. Vogelsinger
   (\)    ICQ #13394035
    ^     http://www.vogelsinger.at/