How do I convice postgres to use an index?

Поиск
Список
Период
Сортировка
От Vic Ricker
Тема How do I convice postgres to use an index?
Дата
Msg-id 40F44635.6050202@ricker.us
обсуждение исходный текст
Список pgsql-sql
I apologize for the following stupid question.  I have been doing some 
searching and haven't found anything really helpful.

The problem is that postgres (7.4.2) keeps choosing to do a sequential 
scan on a table when an index scan would be significantly faster.

The queries that I'm using look at daily statistics from events logged 
by our Checkpoint firewall and generate graphs.  Since they are bit 
complicated, I simplified it to "select count(*) from log where 
timestamp>='7/12/2004'" for testing.

The table looks like this:
   Column    |            Type             | Modifiers
--------------+-----------------------------+-----------loc          | integer                     |src          | inet
                      |dst          | inet                        |interface    | character varying(10)
|direction   | character varying(8)        |proto        | character varying(4)        |service      | integer
          |icmp_code    | integer                     |sport        | integer                     |timestamp    |
timestampwithout time zone |rule         | character varying(8)        |message_info | text
|action      | character varying(16)       |icmp_type    | integer                     |orig         | inet
          |
 
Indexes:   "log_dst_key" btree (dst)   "log_src_key" btree (src)   "log_timestamp_key" btree ("timestamp")


To test, I started with vacuum analyze.  (My table has approximately 
5.8M rows.)


fw1=# select count(*) from log where timestamp>='7/12/2004';count
--------246763
(1 row)

Time: 161199.955 ms
fw1=# set enable_seqscan='off';
SET
Time: 47.662 ms
fw1=# select count(*) from log where timestamp>='7/12/2004';count
--------247149
(1 row)

Time: 12428.210 ms

Notice the execution time differences.

The query plan before turning enable_seqscan off looks like this:

Aggregate  (cost=208963.26..208963.26 rows=1 width=0)  ->  Seq Scan on log  (cost=0.00..208380.89 rows=232948 width=0)
     Filter: ("timestamp" >= '2004-07-12 00:00:00'::timestamp 
 
without time zone)


Any suggestions?

Thanks,
-Vic



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

Предыдущее
От: "SZŰCS Gábor"
Дата:
Сообщение: Re: Constraint->function dependency and dump in 7.3
Следующее
От: Kenneth Gonsalves
Дата:
Сообщение: schemas