X-Original-To: pgsql-performance@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 94E6C476105 for ; Tue, 25 Feb 2003 05:40:03 -0500 (EST) Received: from postgresql.org ([64.49.215.8]) by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with SMTP id 67800-10 for ; Tue, 25 Feb 2003 05:39:52 -0500 (EST) Received: from galilee.canodesc.com (unknown [165.228.0.2]) by postgresql.org (Postfix) with ESMTP id 930354762ED for ; Tue, 25 Feb 2003 03:03:43 -0500 (EST) Received: from carbine.canodesc.com (carbine.canodesc.com [192.168.2.11]) by galilee.canodesc.com (8.11.2/8.11.2/cooper-1.1) with ESMTP id h1P83ge02311 for ; Tue, 25 Feb 2003 19:03:42 +1100 Received: (from root@localhost) by carbine.canodesc.com (8.11.6/8.11.6) id h1P83gV26670 for pgsql-performance@postgresql.org; Tue, 25 Feb 2003 19:03:42 +1100 Received: from poseidon.canodesc.com (poseidon.canodesc.com [192.168.1.10]) by carbine.canodesc.com (8.11.6/8.9.3) with ESMTP id h1P83fp26541 for ; Tue, 25 Feb 2003 19:03:41 +1100 Received: from localhost (localhost [[UNIX: localhost]]) by poseidon.canodesc.com (8.11.6/8.11.6) id h1P83fu13450 for pgsql-performance@postgresql.org; Tue, 25 Feb 2003 19:03:41 +1100 X-scanner: scanned by Inflex 1.0.12.1 - (http://pldaniels.com/inflex/) Content-Type: text/plain; charset="us-ascii" From: Mark Halliwell To: pgsql-performance@postgresql.org Subject: Query not using the index Date: Tue, 25 Feb 2003 19:03:40 +1100 User-Agent: KMail/1.4.1 MIME-Version: 1.0 Message-Id: <200302251903.40364.mark@transportservices.com.au> Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: by amavisd-new X-Archive-Number: 200302/194 X-Sequence-Number: 1250 Hello Everyone, I am having some problems getting my queries to use the correct index, and = as=20 a result they use a sequential scan that takes a very long time. The table in question is used for replicating data between computers and=20 contains over 7 million records. The table is on a few different linux=20 computers, some running postgresql version 7.3 and some 7.3.2; my problem i= s=20 the same on all. The relevent fields are: Table "public.replicate" Column | Type | Modifiers --------------+-----------------------------+----------- computer | integer | not null sequence | integer | not null The majority of records (about 6.8 million) have computer =3D 8 with sequen= ce=20 starting at 2200000 and incrementing by 1. There are about 497000 records with computer =3D 3 with the sequence starti= ng at=20 1 and also incrementing by 1. There are only a few records with other computer numbers. Records are inserted (they are never deleted but sometimes updated) in=20 numerical order by the sequence field for each computer and together these= =20 fields (computer, sequence) are unique. I have a few queries that attempt to find recently inserted records for a= =20 particular computer. Most of my queries include other terms in the where= =20 clause and sort the results (usually by sequence), however this simple quer= y=20 can be used as an example of my problem: select * from replicate where computer =3D 3 and sequence >=3D 490000; I have created several different indexes (always doing a vacuum analyse=20 afterwards etc), but the explain always reports a sequential scan. If I=20 force an index scan, it runs very quickly - as it should. Also, it appears= =20 that if a specify an upper limit for sequence (a value which I cannot alway= s=20 easily predict), it also uses the index. If my query is for those records with computer =3D 8, I believe it chooses = the=20 correct index every time. Here are some examples of the indexes I created and the explains: =3D=3D=3D=3D=3D=3D=3D This is the original index which works fine until there are lots (several= =20 thousand) of records for a particular computer number: create unique index replicate_id on replicate (computer, sequence); explain analyse select * from replicate where computer =3D 3 and sequence >= =3D=20 490000; QUERY PLAN ---------------------------------------------------------------------------= --------------------------------------------- Seq Scan on replicate (cost=3D0.00..400259.20 rows=3D300970 width=3D386) = (actual=20 time=3D80280.18..80974.41 rows=3D7459 loops=3D1) Filter: ((computer =3D 3) AND ("sequence" >=3D 490000)) Total runtime: 80978.67 msec (3 rows) But if we put in an upper limit for the sequence we get: explain analyse select * from replicate where computer =3D 3 and sequence >= =3D=20 490000 and sequence < 600000; QUERY PLAN ---------------------------------------------------------------------------= ------------------------------------------------------- Index Scan using replicate_id on replicate (cost=3D0.00..625.99 rows=3D18= 2=20 width=3D388) (actual time=3D45.00..446.31 rows=3D7789 loops=3D1) Index Cond: ((computer =3D 3) AND ("sequence" >=3D 490000) AND ("sequenc= e" <=20 600000)) Total runtime: 451.18 msec (3 rows) set enable_seqscan=3Doff; explain analyse select * from replicate where computer =3D 3 and sequence >= =3D=20 490000; QUERY PLAN ---------------------------------------------------------------------------= ----------------------------------------------------------- Index Scan using replicate_id on replicate (cost=3D0.00..991401.16 rows= =3D289949=20 width=3D388) (actual time=3D0.06..47.84 rows=3D7788 loops=3D1) Index Cond: ((computer =3D 3) AND ("sequence" >=3D 490000)) Total runtime: 52.48 msec (3 rows) =3D=3D=3D=3D=3D=3D I tried adding this index, and it seemed to work until (I think) there were= =20 about 400000 records for computer =3D 3. create index replicate_computer_3 on replicate (sequence) WHERE computer = =3D 3; explain analyse select * from replicate where computer =3D 3 and sequence >= =3D=20 490000; QUERY PLAN ---------------------------------------------------------------------------= --------------------------------------------- Seq Scan on replicate (cost=3D0.00..400262.91 rows=3D287148 width=3D386) = (actual=20 time=3D74371.70..74664.84 rows=3D7637 loops=3D1) Filter: ((computer =3D 3) AND ("sequence" >=3D 490000)) Total runtime: 74669.22 msec (3 rows) But if we put an upper limit for the sequence we get: explain analyse select * from replicate where computer =3D 3 and sequence >= =3D=20 490000 and sequence < 600000; QUERY PLAN ---------------------------------------------------------------------------= ------------------------------------------------------------- Index Scan using replicate_computer_3 on replicate (cost=3D0.00..417.29= =20 rows=3D180 width=3D386) (actual time=3D0.06..54.21 rows=3D7657 loops=3D1) Index Cond: (("sequence" >=3D 490000) AND ("sequence" < 600000)) Filter: (computer =3D 3) Total runtime: 58.86 msec (4 rows) And, forcing the index: set enable_seqscan=3Doff; explain analyse select * from replicate where computer =3D 3 and sequence >= =3D=20 490000; QUERY PLAN ---------------------------------------------------------------------------= ------------------------------------------------------------------- Index Scan using replicate_computer_3 on replicate (cost=3D0.00..660538.2= 8=20 rows=3D287148 width=3D386) (actual time=3D0.06..53.05 rows=3D7657 loops=3D1) Index Cond: ("sequence" >=3D 490000) Filter: (computer =3D 3) Total runtime: 57.66 msec (4 rows) I have tried quiet a few different indexes and they all seem to do the same= =20 sort of thing. Is there anything that I should be doing to make these=20 queries use the index? I am unable to easily predict what the upper limit= =20 for the sequence should be in all cases, so I would rather a solution that= =20 didn't require specifying it. Am I missing something? Is there some other information that I should have provided? Thanks Mark Halliwell