X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 93A25329F0D for ; Wed, 8 Sep 2004 01:49:09 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 40731-05 for ; Wed, 8 Sep 2004 00:49:01 +0000 (GMT) Received: from www.postgresql.com (www.postgresql.com [200.46.204.209]) by svr1.postgresql.org (Postfix) with ESMTP id 50AE6329D8A for ; Wed, 8 Sep 2004 01:49:02 +0100 (BST) Received: from ctg-msnexc01.staff.berbee.com (msn-office-flr2.binc.net [64.73.12.254]) by www.postgresql.com (Postfix) with ESMTP id 2857C5A1173 for ; Tue, 7 Sep 2004 22:32:48 +0100 (BST) Received: from localhost ([172.30.254.220] RDNS failed) by ctg-msnexc01.staff.berbee.com with Microsoft SMTPSVC(6.0.3790.0); Tue, 7 Sep 2004 16:32:31 -0500 From: "Jeremy M. Guthrie" Reply-To: jeremy.guthrie@berbee.com Organization: Berbee Information Networks Inc. To: pgsql-performance@postgresql.org Subject: Stuck using Sequential Scan Date: Tue, 7 Sep 2004 16:32:03 -0500 User-Agent: KMail/1.6.2 MIME-Version: 1.0 Content-Disposition: inline Content-Type: Text/Plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Message-Id: <200409071632.05374.jeremy.guthrie@berbee.com> X-OriginalArrivalTime: 07 Sep 2004 21:32:31.0575 (UTC) FILETIME=[2E8EBE70:01C49522] X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200409/69 X-Sequence-Number: 8197 -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 I have a problem where I have the table format listed below. I have the=20 primary key tsyslog_id and the index built against it. However, when I=20 select a unique row, it will only ever do a seq scan even after I turn off= =20 all other types except indexscan. I understand you cannot fully turn off s= eq=20 scan.=20=20 Syslog_TArchive size: 1,426,472,960 bytes syslog_tarchive_pkey size: 132,833,280 bytes archhost_idx size: 300,802,048 bytes tarchdatetime_idx size: 159,293,440 bytes tarchhostid_idx size: 362,323,968 bytes I cannot run vacuum more than once a day because of its heavy IO penalty. = I=20 run analyze once an hour. However, if I run analyze then explain, I see no= =20 difference in the planners decisions. What am I missing? TSyslog=3D# \d syslog_tarchive; Table "public.syslog_tarchive" Column | Type |=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 Modifiers - ------------+------------------------+-----------------------------------= -------------------------------------- tsyslog_id | bigint | not null default=20 nextval('public.syslog_tarchive_tsyslog_id_seq'::text) facility | integer | severity | integer | date | date | time | time without time zone | host | character varying(128) | message | text | Indexes: "syslog_tarchive_pkey" primary key, btree (tsyslog_id) "archhost_idx" btree (host) "tarchdatetime_idx" btree (date, "time") "tarchhostid_idx" btree (tsyslog_id, host) TSyslog=3D# explain select * from tsyslog where tsyslog_id=3D431650835; QUERY PLAN - ------------------------------------------------------------------------- Seq Scan on tsyslog (cost=3D100000000.00..100000058.20 rows=3D2 width=3D1= 87) Filter: (tsyslog_id =3D 431650835) (2 rows) - --=20 - -------------------------------------------------- Jeremy M. Guthrie jeremy.guthrie@berbee.com Senior Network Engineer Phone: 608-298-1061 Berbee Fax: 608-288-3007 5520 Research Park Drive NOC: 608-298-1102 Madison, WI 53711 -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.4 (GNU/Linux) iD8DBQFBPijTqtjaBHGZBeURAndgAJ4rT2NpG9aGAdogoZaV+BvUfF6TjACfaexf LrBzhDQK72u8dCUuPOSHB+Y=3D =3DDSxi -----END PGP SIGNATURE-----