Re: Inconsistent usage of Index
От | Joe Conway |
---|---|
Тема | Re: Inconsistent usage of Index |
Дата | |
Msg-id | 005701c0f100$82acca70$0205a8c0@jecw2k1 обсуждение исходный текст |
Ответ на | Inconsistent usage of Index (Subra Radhakrishnan <subra100@yahoo.com>) |
Список | pgsql-sql |
> Instance #2 > ----------- > explain select * from allied_medical_req_main where v_ttyp_code = 'XRAY'; > NOTICE: QUERY PLAN: > > Seq Scan on allied_medical_req_main (cost=0.00..79.49 rows=713 width=192) > > EXPLAIN > As you can see from Instance #1 and #2 above, the usage of Index is not consistent. Do you > have any suggestions? > > Thanks, > > Subra > > P.S: I also did vacuum on the database. However, I am not clear as to what actually it does. Among other things, "vacuum analyze" calculates statistics for the table columns, which are used by the optimizer to decide how to most efficiently execute the query. See http://www.postgresql.org/idocs/index.php?sql-vacuum.html for an explanation. See http://www.postgresql.org/idocs/index.php?performance-tips.html#USING-EXPLAI N for information on how to interpret the explain output. The output above on instance 2 shows 713 rows expected output. If this is a significant percent of the total number of rows in this table, then it *is* more efficient to perform a table scan instead of using the index. What is the total number or rows in this table? If you still really want to force an index scan, try issuing "SET ENABLE_SEQSCAN = OFF;". See http://www.postgresql.org/idocs/index.php?runtime-config.html#RUNTIME-CONFIG -OPTIMIZER for an explanation. Hope this helps, -- Joe
В списке pgsql-sql по дате отправления: