Re: Query planner question

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/