Re: [Sqlgrey-users] Indexes

[Michel B. <mi...@bo...>]

Le Lundi 07 F=E9vrier 2005 16:27, Michel Bouissou a =E9crit :
> > >
> > >Without this index, PostgreSQL has to read the whole table sequentia=
lly,
> >
> > Under normal circumstances it shouldn't do a sequential scan : the
> > primary key index should be used.
>
> Not when the SQL query involves only (sender_domain, host_ip) as does
> SQLgrey 1.4.2 lines 646 and 692-693.
>
> These queries don't use the leftmost column of the primay key
> =ABfrom_awl_pkey=BB cl=E9 primaire, btree (sender_name, sender_domain, =
host_ip),
> and, per file:/usr/share/doc/postgresql-docs-7.4.5/indexes-multicolumn.=
html
> :
> <<<
> The query planner can use a multicolumn index for queries that involve =
the
> leftmost column in the index definition plus any number of columns list=
ed
> to the right of it, without a gap. For example, an index on (a, b, c) c=
an
> be used in queries involving all of a, b, and c, or in queries involvin=
g
> both a and b, or in queries involving only a, but not in other combinat=
ions
> >>>

But of course, if you decided that you prefer to modify the order of the=20
primary key, and turn it into (host_ip, sender_domain, sender_name), then=
 it=20
could be used both for requests involving all of the 3 fields, or for=20
requests involving only (host_ip, sender_domain).

The global organization of SQLgrey makes me feel that (host_ip, sender_do=
main,=20
sender_name) would probably be the best order for the indexes on all tabl=
es=20
(except for domain_awl that has no sender_name of course).

--=20
Michel Bouissou <mi...@bo...> OpenPGP ID 0xDDE8AC6E