Thread: 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