Re: [Sqlgrey-users] Indexes

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

Le Lundi 07 F=E9vrier 2005 15:29, Lionel Bouton a =E9crit :
> Michel Bouissou wrote the following on 02/07/2005 02:52 PM :
> >I tried to figure out what kind of indexes could be useful in SQLgrey,=
 and
> > I found one that my PostgreSQL seems to like on from_awl:
> >
> >=ABfrom_awl_sender_domain_host_ip=BB btree (sender_domain, host_ip)
> >
> >For each time a new entry is to be added in from_awl, SQLgrey will loo=
k
> > for the count(*) of addresses with the same domain and IP, to determi=
ne
> > whether this couple should be moved to domain_awl.
> >
> >Without this index, PostgreSQL has to read the whole table sequentiall=
y,
>
> 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 SQL=
grey=20
1.4.2 lines 646 and 692-693.

These queries don't use the leftmost column of the primay key =ABfrom_awl=
_pkey=BB=20
cl=E9 primaire, btree (sender_name, sender_domain, host_ip), and, per=20
file:/usr/share/doc/postgresql-docs-7.4.5/indexes-multicolumn.html :

<<<
The query planner can use a multicolumn index for queries that involve th=
e=20
leftmost column in the index definition plus any number of columns listed=
 to=20
the right of it, without a gap. For example, an index on (a, b, c) can be=
=20
used in queries involving all of a, b, and c, or in queries involving bot=
h a=20
and b, or in queries involving only a, but not in other combinations
>>>

> However it is very well possible that the index you advise would be
> scanned faster than the primary key index as it is the ideal index for
> the task unlike the PKEY.

Not only, but mainly because the primary key can't be used...

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