[OGo-Developer] Locating Participants For New CalDAV Appointments

[Adam Tauno Williams]

--=-627eRNQ1Kr41hEvuW82x
Content-Type: text/plain
Content-Transfer-Encoding: quoted-printable

> I think thats unlikely. Probably it doesn't cache results or its a =20
> plain bug.

Something like that,  I've got SxCachePath set but it doesn't seem to be
caching anything there. I was expecting it to cache the fetches but it
looks like it is re-rendering everything if I must move back on forth
between two months.

> > SELECT DISTINCT c1.company_id AS pkey   <--- GOT ME
> > FROM company_value cv, person c1
> > WHERE c1.is_person=3D1 AND (c1.is_account=3D1)
> >   AND (c1.is_private=3D0 OR c1.is_private IS NULL)
> >   AND ( lower(cv.value_string) =3D 'adam@morrison-ind.com'
> >   AND lower(cv.attribute) =3D 'email1'
> >   AND (c1.db_status <> 'archived')
> >   AND c1.company_id =3D cv.company_id );
> Oh well, this looks stupid anyways ;-) Should be something like
> etc. Probably the SQL needs some refactoring. Though most likely =20
> PostgreSQL optimizes the stupidness away ;-)

To some degree;  but a proper index helps allot in ZideStore anyway,
which seems to do allot of look-ups by e-mail address.

CREATE INDEX company_value_aux000 ON company_value(LOWER(value_string),
LOWER(attribute));

Even when the non-index query is executed repeatedly, so you are getting
most pages from the cache, having the index beats it by at least 100ms;
instead of getting a sequential scan on company_value - with two calls
to LOWER for every record - you get a bitmap scan of the index.

I don't think it would hurt to rethink some of the indexing in the DB.

EXPLAIN ANALYZE SELECT DISTINCT c1.company_id AS pkey
FROM company_value cv, person c1=20
WHERE c1.is_person=3D1 AND (c1.is_account=3D1)=20
  AND (c1.is_private=3D0 OR c1.is_private IS NULL)=20
  AND ( lower(cv.value_string) =3D 'adam@morrison-ind.com'=20
  AND lower(cv.attribute) =3D 'email1'=20
  AND (c1.db_status <> 'archived')=20
  AND c1.company_id =3D cv.company_id );

***************************
Query plan without index
***************************
 Unique  (cost=3D4061.23..4061.24 rows=3D1 width=3D4) (actual
time=3D190.304..190.306 rows=3D1 loops=3D1)
   ->  Sort  (cost=3D4061.23..4061.24 rows=3D1 width=3D4) (actual
time=3D190.300..190.300 rows=3D1 loops=3D1)
         Sort Key: c1.company_id
         ->  Nested Loop  (cost=3D0.00..4061.22 rows=3D1 width=3D4) (actual
time=3D3.466..190.284 rows=3D1 loops=3D1)
               ->  Seq Scan on company_value cv  (cost=3D0.00..4043.26
rows=3D3 width=3D4) (actual time=3D3.362..190.174 rows=3D1 loops=3D1)
                     Filter: ((lower((value_string)::text) =3D
'adam@morrison-ind.com'::text) AND (lower((attribute)::text) =3D
'email1'::text))
               ->  Index Scan using person_aux001_idx on person c1
(cost=3D0.00..5.97 rows=3D1 width=3D4) (actual time=3D0.096..0.098 rows=3D1
loops=3D1)
                     Index Cond: (c1.company_id =3D "outer".company_id)
                     Filter: ((is_person =3D 1) AND (is_account =3D 1) AND
((is_private =3D 0) OR (is_private IS NULL)))
 Total runtime: 190.427 ms
(10 rows)

************************
Query plan with index
************************
 Unique  (cost=3D31.64..31.65 rows=3D1 width=3D4) (actual time=3D0.096..0.0=
98
rows=3D1 loops=3D1)
   ->  Sort  (cost=3D31.64..31.65 rows=3D1 width=3D4) (actual
time=3D0.095..0.095 rows=3D1 loops=3D1)
         Sort Key: c1.company_id
         ->  Nested Loop  (cost=3D2.02..31.63 rows=3D1 width=3D4) (actual
time=3D0.079..0.083 rows=3D1 loops=3D1)
               ->  Bitmap Heap Scan on company_value cv
(cost=3D2.02..13.67 rows=3D3 width=3D4) (actual time=3D0.054..0.055 rows=3D=
1
loops=3D1)
                     Recheck Cond: ((lower((value_string)::text) =3D
'adam@morrison-ind.com'::text) AND (lower((attribute)::text) =3D
'email1'::text))
                     ->  Bitmap Index Scan on company_value_aux000
(cost=3D0.00..2.02 rows=3D3 width=3D0) (actual time=3D0.048..0.048 rows=3D1
loops=3D1)
                           Index Cond: ((lower((value_string)::text) =3D
'adam@morrison-ind.com'::text) AND (lower((attribute)::text) =3D
'email1'::text))
               ->  Index Scan using person_aux001_idx on person c1
(cost=3D0.00..5.97 rows=3D1 width=3D4) (actual time=3D0.020..0.022 rows=3D1
loops=3D1)
                     Index Cond: (c1.company_id =3D "outer".company_id)
                     Filter: ((is_person =3D 1) AND (is_account =3D 1) AND
((is_private =3D 0) OR (is_private IS NULL)))
 Total runtime: 0.216 ms


--=-627eRNQ1Kr41hEvuW82x
Content-Type: application/pgp-signature; name=signature.asc
Content-Description: This is a digitally signed message part

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.5 (GNU/Linux)

iD8DBQBGRHCpLRePpNle04MRAvEYAJ9/fJbsto6Td32VDhImQJUvxcAJ7QCfQHUs
HwWoUj2jCVux64l/WkdFjyo=
=lGnY
-----END PGP SIGNATURE-----

--=-627eRNQ1Kr41hEvuW82x--