Обсуждение: Query not using index
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=# \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 NULL "croute_netwokr_valid_at" btree (network, valid_at) "croute_network" btree (network) WHERE archived_at IS NULL "croute_network_all" btree (network) iplog=# 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=> explain analyze SELECT * customer-> FROM croute customer-> WHERE '193.68.0.8/32' <<= 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=0.00..441.62 rows=413 width=102) (actual time=14.131..37.515 rows=1 loops=1) Filter: (('193.68.0.8/32'::cidr <<= 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=> 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=# set enable_seqscan = off; SET iplog=# explain analyze SELECT * iplog-# FROM croute iplog-# WHERE '193.68.0.8/32' <<= 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=100000000.00..100000780.64 rows=1030 width=103) (actual time=29.593..29.819 rows=1 loops=1) Filter: (('193.68.0.8/32'::cidr <<= 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=# CREATE INDEX croute_all on croute(network,archived_at,valid_at,confid); CREATE INDEX iplog=# explain analyze SELECT * iplog-# FROM croute iplog-# WHERE '193.68.0.8/32' <<= 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=100000000.00..100000780.64 rows=1030 width=103) (actual time=29.626..29.879 rows=1 loops=1) Filter: (('193.68.0.8/32'::cidr <<= 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
On 12/9/05, Kaloyan Iliev <news1@faith.digsys.bg> 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=# \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 NULL > "croute_netwokr_valid_at" btree (network, valid_at) > "croute_network" btree (network) WHERE archived_at IS NULL > "croute_network_all" btree (network) > > > iplog=# 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=> explain analyze SELECT * > customer-> FROM croute > customer-> WHERE '193.68.0.8/32' <<= > 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=0.00..441.62 rows=413 width=102) (actual > time=14.131..37.515 rows=1 loops=1) > Filter: (('193.68.0.8/32'::cidr <<= 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=> 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=# set enable_seqscan = off; > SET > iplog=# explain analyze SELECT * > iplog-# FROM croute > iplog-# WHERE '193.68.0.8/32' <<= > 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=100000000.00..100000780.64 rows=1030 > width=103) (actual time=29.593..29.819 rows=1 loops=1) > Filter: (('193.68.0.8/32'::cidr <<= 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=# CREATE INDEX croute_all on > croute(network,archived_at,valid_at,confid); > CREATE INDEX > iplog=# explain analyze SELECT * > iplog-# FROM croute > iplog-# WHERE '193.68.0.8/32' <<= > 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=100000000.00..100000780.64 rows=1030 > width=103) (actual time=29.626..29.879 rows=1 loops=1) > Filter: (('193.68.0.8/32'::cidr <<= 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' <<= 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' <<= 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 ;)
Hi all, Thanks for the reply. I made some more test and find out that the problem is with the <<= operator for the network type. Can I create index which to work with <<=. Because if I use = the index is used. But not for <<=. iplog=# explain analyze SELECT * iplog-# FROM croute iplog-# WHERE '193.68.0.10/32' <<= network; QUERY PLAN --------------------------------------------------------------------------------------------------------- Seq Scan on croute (cost=0.00..707.27 rows=4891 width=103) (actual time=10.313..29.621 rows=2 loops=1) Filter: ('193.68.0.10/32'::cidr <<= network) Total runtime: 29.729 ms (3 rows) iplog=# explain analyze SELECT * iplog-# FROM croute iplog-# WHERE '193.68.0.10/32' = network; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------ Index Scan using croute_network_all on croute (cost=0.00..17.99 rows=4 width=103) (actual time=0.053..0.059 rows=1 loops=1) Index Cond: ('193.68.0.10/32'::cidr = network) Total runtime: 0.167 ms (3 rows) Waiting for replies. Thanks to all in advance. Kaloyan Iliev
Hi all, Thanks for the reply. I made some more test and find out that the problem is with the <<= operator for the network type. Can I create index which to work with <<=. Because if I use = the index is used. But not for <<=. iplog=# explain analyze SELECT * iplog-# FROM croute iplog-# WHERE '193.68.0.10/32' <<= network; QUERY PLAN --------------------------------------------------------------------------------------------------------- Seq Scan on croute (cost=0.00..707.27 rows=4891 width=103) (actual time=10.313..29.621 rows=2 loops=1) Filter: ('193.68.0.10/32'::cidr <<= network) Total runtime: 29.729 ms (3 rows) iplog=# explain analyze SELECT * iplog-# FROM croute iplog-# WHERE '193.68.0.10/32' = network; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------ Index Scan using croute_network_all on croute (cost=0.00..17.99 rows=4 width=103) (actual time=0.053..0.059 rows=1 loops=1) Index Cond: ('193.68.0.10/32'::cidr = network) Total runtime: 0.167 ms (3 rows) Waiting for replies. Thanks to all in advance. Kaloyan Iliev