Seq Scan but I think it should be Index Scan

Поиск
Список
Период
Сортировка
От Edoceo Lists
Тема Seq Scan but I think it should be Index Scan
Дата
Msg-id 436033AA.9030900@edoceo.com
обсуждение исходный текст
Ответы Re: Seq Scan but I think it should be Index Scan  (Michael Fuhr <mike@fuhr.org>)
Список pgsql-general
So the details of it:
I'm using PostgreSQL 8.0.3 on a Gentoo kernel 2.6.12 on a P4/2.8G+HT proc, with kernel RAID0 on some SATA drives and 1G

RAM.  Don't know the bus speed.  I'm thinking that my queries are not using indexs correctly and therefore taking
longer 
to complete than they should.  I've put the details below, but changed some names.  If anyone could shed some light?

pg_config  --configure
'--prefix=/usr' '--mandir=/usr/share/man' '--host=i686-pc-linux-gnu' '--with-docdir=/usr/share/doc/postgresql-8.0.3'
'--libdir=/usr/lib' '--enable-depend' '--with-gnu-ld' '--with-perl' '--with-openssl' '--enable-nls' '--with-pam'
'CFLAGS=-O2 -march=pentium4' 'host_alias=i686-pc-linux-gnu'


data=# explain analyze select count(id) from x_base where x_type < 100 and x_date<='2005-10-26' and x_time<'06:00:00';
                                                           QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------
  Aggregate  (cost=539453.36..539453.36 rows=1 width=4) (actual time=66200.763..66200.764 rows=1 loops=1)
    ->  Seq Scan on x_base  (cost=0.00..539136.18 rows=126871 width=4) (actual time=31618.624..66174.710 rows=37451
loops=1)
          Filter: ((x_type < 100) AND (x_date <= '2005-10-26'::date) AND (x_time < '06:00:00'::time without time zone))
  Total runtime: 66200.811 ms

data=# \d x_base
                                          Table "public.x_base"
        Column        |          Type          |                        Modifiers
---------------------+------------------------+----------------------------------------------------------
  id                  | integer                | not null default nextval('public.x_base_id_seq'::text)
  x_code              | character(8)           |
  x_date              | date                   | not null
  x_time              | time without time zone | not null
  a                   | character(1)           |
  b                   | integer                |
  c                   | character(5)           |
  d                   | character(16)          |
  e                   | character(1)           |
  f                   | character(1)           |
  g                   | character(10)          |
  h                   | character(1)           |
  i                   | character(1)           |
  j                   | character varying(32)  |
  k                   | integer                |
  l                   | integer                |
  m                   | integer                |
  n                   | character varying(32)  |
  o                   | integer                |
  p                   | character varying(14)  |
  q                   | integer                |
Indexes:
     "x_base_pkey" PRIMARY KEY, btree (id)
     "ix_d_cd" btree (x_date)
     "ix_t_cb" btree (x_type)
Foreign-key constraints:
     "fk_k_id" FOREIGN KEY (k) REFERENCES x_file(id)

Now, see that x_type index?  Why didn't this thing Index Scan "ix_t_cb" on that column?  Me thinks if it had my query
would be much faster.  Or perhaps if I only where x_type?  I tried that but it still took a minute.  I took out count()

and it still took a minute.  Always using Seq Scan, am I doing something dumb here?  There are more than six million
records in that table, maybe thats just how long it takes?  Perhaps I should change architecture or schema to improve
performance?  Tweak the log? Thanks.

/djb


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

Предыдущее
От: Michael Fuhr
Дата:
Сообщение: Re: Variable return type...
Следующее
От: "Cristian Prieto"
Дата:
Сообщение: Re: Variable return type...