X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 116FE9DCAB7 for ; Fri, 9 Dec 2005 11:06:05 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 07059-02 for ; Fri, 9 Dec 2005 11:06:09 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from zproxy.gmail.com (zproxy.gmail.com [64.233.162.205]) by postgresql.org (Postfix) with ESMTP id D59B19DCB68 for ; Fri, 9 Dec 2005 11:06:02 -0400 (AST) Received: by zproxy.gmail.com with SMTP id n29so984884nzf for ; Fri, 09 Dec 2005 07:06:07 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=GWvoWeJwKzrUebv+HCmunyz2IStVPxoo8Kq7O4uVCQAm+8189Ywoua1J81c0MUuJXUA7oqdGM+xMK2bEfD6cJZwbi8nr+JfqiUjsI7k6qO4FBE2xeNgdawu3W98hJWhhiL99fESxjmKPi7WaNccoYVOFKsiBF3iBeyJdBjpJn30= Received: by 10.64.253.17 with SMTP id a17mr3909183qbi; Fri, 09 Dec 2005 07:06:07 -0800 (PST) Received: by 10.65.180.14 with HTTP; Fri, 9 Dec 2005 07:06:07 -0800 (PST) Message-ID: Date: Fri, 9 Dec 2005 10:06:07 -0500 From: Jaime Casanova To: Kaloyan Iliev Subject: Re: Query not using index Cc: pgsql-performance@postgresql.org In-Reply-To: <4399500A.4020607@faith.digsys.bg> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline References: <4399500A.4020607@faith.digsys.bg> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.194 required=5 tests=[AWL=0.194] X-Spam-Score: 0.194 X-Spam-Level: X-Archive-Number: 200512/259 X-Sequence-Number: 16080 On 12/9/05, Kaloyan Iliev wrote: > Hi all, > > I have a problem with a query which doeson't want to use indexes. I > tried to create different indexes but nothing help. Can anyone suggest > what index I need. > This query is executed 1.5Milion times per day and I need it to be veri > fast. I made my test on 8.0.0 beta but the production database is still > 7.4.6 so i need suggestions for 7.4.6. > I will post the table with the indexes and the query plans. > iplog=3D# \d croute > Table "public.croute" > Column | Type | Modifiers > -----------------+--------------------------+----------- > confid | integer | > network | cidr | > comment | text | > router | text | > port | text | > valid_at | timestamp with time zone | > archived_at | timestamp with time zone | > Indexes: > "croute_netwo" btree (network) WHERE confid > 0 AND archived_at IS NUL= L > "croute_netwokr_valid_at" btree (network, valid_at) > "croute_network" btree (network) WHERE archived_at IS NULL > "croute_network_all" btree (network) > > > iplog=3D# select version(); > version > -------------------------------------------------------------------------= ------------------------------------- > PostgreSQL 8.0.0beta1 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) > 3.3.2 (Mandrake Linux 10.0 3.3.2-6mdk) > (1 row) > > !!!!!!!!!!!!THIS IS THE QUERY!!!!!!!!!!!!!!!!! > customer=3D> explain analyze SELECT * > customer-> FROM croute > customer-> WHERE '193.68.0.8/32' <<=3D > network AND > customer-> (archived_at is NULL > OR archived_at > '17-11-2005') AND > customer-> valid_at < > '1-12-2005'::date AND > customer-> confid > 0; > > QUERY PLAN > -------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ----- > Seq Scan on croute (cost=3D0.00..441.62 rows=3D413 width=3D102) (actual > time=3D14.131..37.515 rows=3D1 loops=3D1) > Filter: (('193.68.0.8/32'::cidr <<=3D network) AND ((archived_at IS > NULL) OR (archived_at > '2005-11-17 00:00:00+02'::timestamp with time > zone)) AND (valid_at < ('2005-12-01'::date)::timestamp with time zone) > AND (confid > 0)) > Total runtime: 37.931 ms > (3 rows) > > customer=3D> select count(*) from croute; > count > ------- > 10066 > (1 row) > This is the result of the query: > confid | network | comment | router | port | > valid_at | archived_at | > -------+---------------+---------+------+----+-------------------------+-= ----------+ > 19971 | xx.xx.xx.xx/32 | xxxxx | ? | ? | 2005-03-11 > 00:00:00+02 | | > (1 row) > And last I try to stop the sequance scan but it doesn't help. I suppose > I don't have the right index. > iplog=3D# set enable_seqscan =3D off; > SET > iplog=3D# explain analyze SELECT * > iplog-# FROM croute > iplog-# WHERE '193.68.0.8/32' <<=3D > network AND > iplog-# (archived_at is NULL OR > archived_at > '17-11-2005') AND > iplog-# valid_at < > '1-12-2005'::date AND > iplog-# confid > 0; > > QUERY PLAN > -------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------- > Seq Scan on croute (cost=3D100000000.00..100000780.64 rows=3D1030 > width=3D103) (actual time=3D29.593..29.819 rows=3D1 loops=3D1) > Filter: (('193.68.0.8/32'::cidr <<=3D network) AND ((archived_at IS > NULL) OR (archived_at > '2005-11-17 00:00:00+02'::timestamp with time > zone)) AND (valid_at < '2005-12-01'::date) AND (confid > 0)) > Total runtime: 29.931 ms > (3 rows) > > I try creating one last index on all fields but it doesn't help. > iplog=3D# CREATE INDEX croute_all on > croute(network,archived_at,valid_at,confid); > CREATE INDEX > iplog=3D# explain analyze SELECT * > iplog-# FROM croute > iplog-# WHERE '193.68.0.8/32' <<=3D > network AND > iplog-# (archived_at is NULL OR > archived_at > '17-11-2005') AND > iplog-# valid_at < > '1-12-2005'::date AND > iplog-# confid > 0; > > QUERY PLAN > -------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------- > Seq Scan on croute (cost=3D100000000.00..100000780.64 rows=3D1030 > width=3D103) (actual time=3D29.626..29.879 rows=3D1 loops=3D1) > Filter: (('193.68.0.8/32'::cidr <<=3D network) AND ((archived_at IS > NULL) OR (archived_at > '2005-11-17 00:00:00+02'::timestamp with time > zone)) AND (valid_at < '2005-12-01'::date) AND (confid > 0)) > Total runtime: 30.060 ms > (3 rows) > > > Thanks in advance to all. > > Kaloyan Iliev > > In oracle you can use this instead... SELECT * FROM croute WHERE '193.68.0.8/32' <<=3D network AND archived_at is NULL AND valid_at < '1-12-2005'::date AND confid > 0; UNION SELECT * FROM croute WHERE '193.68.0.8/32' <<=3D network AND archived_at > '17-11-2005'::date AND valid_at < '1-12-2005'::date AND confid > 0; although i think that your query can make use of bitmap index in 8.1 -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;)