Re: [Sqlgrey-users] Indexes

[Lionel B. <lio...@bo...>]

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, a=
nd I=20
>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 look =
for=20
>the count(*) of addresses with the same domain and IP, to determine whet=
her=20
>this couple should be moved to domain_awl.
>
>Without this index, PostgreSQL has to read the whole table sequentially,
>

Under normal circumstances it shouldn't do a sequential scan : the=20
primary key index should be used.
However it is very well possible that the index you advise would be=20
scanned faster than the primary key index as it is the ideal index for=20
the task unlike the PKEY.

I'm really interested in numbers on this : could you run the SELECT=20
count(*) with and without this index (after an ANALYZE to make sure the=20
optimizer doesn't fall back to sequential scan because of borked=20
statistics).

Anyone could do the same for SQLite and MySQL ?

> and=20
>the from_awl is a tables that grows big...
>
> =20
>

Yep

Lionel.