Query not using index

Поиск
Список
Период
Сортировка
От Kaloyan Iliev
Тема Query not using index
Дата
Msg-id 4399500A.4020607@faith.digsys.bg
обсуждение исходный текст
Ответы Re: Query not using index  (Jaime Casanova <systemguards@gmail.com>)
Re: Query not using index  (Kaloyan Iliev <news1@faith.digsys.bg>)
Re: Query not using index  (Kaloyan Iliev <kaloyan@mbox.digsys.bg>)
Список pgsql-performance
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


В списке pgsql-performance по дате отправления:

Предыдущее
От: David Lang
Дата:
Сообщение: Re: opinion on disk speed
Следующее
От: "Jeremy Haile"
Дата:
Сообщение: Re: opinion on disk speed