X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 3B680D1D237 for ; Fri, 23 Jan 2004 18:59:09 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 30019-06 for ; Fri, 23 Jan 2004 14:58:39 -0400 (AST) Received: from front1.mail.megapathdsl.net (front1.mail.megapathdsl.net [66.80.60.31]) by svr1.postgresql.org (Postfix) with ESMTP id ACBF8D1E37D for ; Fri, 23 Jan 2004 14:58:37 -0400 (AST) Received: from [64.32.131.193] (HELO [192.168.1.11]) by front1.mail.megapathdsl.net (CommuniGate Pro SMTP 4.1.3) with ESMTP id 141523588 for pgsql-performance@postgresql.org; Fri, 23 Jan 2004 10:58:31 -0800 Subject: help with dual indexing From: Orion Henry To: pgsql-performance@postgresql.org Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="=-IYXSAc2XhbJXrS8f4n/z" Organization: Message-Id: <1074884311.10503.133.camel@orthanc> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.2.2 (1.2.2-5) Date: 23 Jan 2004 10:58:31 -0800 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200401/241 X-Sequence-Number: 5415 --=-IYXSAc2XhbJXrS8f4n/z Content-Type: text/plain Content-Transfer-Encoding: quoted-printable I've got a table with about 10 million events in it. Each has a user_id (about 1000 users) and a event_time timestamp covering a 4 year period with about 50% of the events being in the last year. Some users have only dozens of events. A few have hundreds of thousands. The queries usually are in the form of, where "user_id =3D something and event_time between something and something". Half of my queries index off of the user_id and half index off the event_time. I was thinking this would be a perfect opportunity to use a dual index of (user_id,event_time) but I'm confused as to weather this will help considering the size of this index given that there very few tuples that have the exact same timestamp as another and I'm not sure which order to put the user_id/event_time as I don't know what is meant when people on this list ask which is more selective. Also, would it make sense for me to raise my ANALYZE value and how would I go about doing this? Thanks for the help. --=20 Orion Henry --=-IYXSAc2XhbJXrS8f4n/z Content-Type: application/pgp-signature; name=signature.asc Content-Description: This is a digitally signed message part -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.1 (GNU/Linux) iD8DBQBAEW7X9IoQcq23xtARAv66AKCyHsh3uotV+dplIqIoOiFFs6P7aQCcDdl2 JROFhwPNjzDa1YHYRzYLmFA= =7Nau -----END PGP SIGNATURE----- --=-IYXSAc2XhbJXrS8f4n/z--