From: | "Alain Lavigne" <alavigne(at)zaq(dot)com> |
---|---|
To: | <pgsql-sql(at)postgresql(dot)org> |
Subject: | Need help on a troublesome query plan |
Date: | 2002-07-16 17:24:38 |
Message-ID: | C34466F5BBA39949A8F5C180022EC2064846DB@zaq-msg-03.corp.zaq.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On PostgreSQL Version 7.2.1 on Redhat Linux 7.1
Table bld_tb_bus_fact definition
================================
Column | Type | Modifiers
-----------------+-------------------------- +-----------
bus_fact_id | bigint | not null
bus_fact_ts | timestamp with time zone | not null
party_id | bigint |
svc_id | bigint | not null
bus_fact_data | text | not null
bus_fact_typ_cd | character(10) | not null
bus_fact_kywrd | character varying(300) |
cont_id | bigint |
perfby_id | bigint |
Index "bld_x1_tb_bus_fact"
Column | Type
-----------------+---------------
party_id | bigint
bus_fact_typ_cd | character(10)
cont_id | bigint
btree
With the following query on 50000 records:
explain
SELECT bld_TB_BUS_FACT.BUS_FACT_ID AS id
FROM bld_TB_BUS_FACT
WHERE bld_TB_BUS_FACT.PARTY_ID=1320677
AND bld_TB_BUS_FACT.BUS_FACT_TYP_CD='MSG_SENT'
AND bld_TB_BUS_FACT.CONT_ID=786448
AND bld_TB_BUS_FACT.BUS_FACT_KYWRD ILIKE '%MT-ID=3407979%'
AND bld_TB_BUS_FACT.BUS_FACT_KYWRD ILIKE '%S-ID=1310723%'
limit 1;
psql:test.sql:9: NOTICE: QUERY PLAN:
Limit (cost=0.00..2264.16 rows=1 width=8)
-> Seq Scan on bld_tb_bus_fact (cost=0.00..2264.16 rows=1 width=8)
EXPLAIN
I don't understand why it's not using the defined index, even after performing VACUUM FULL ANALYZE on the table.
I tried disabling seqscan but that didn't change anything.
I'm open to suggestions anyone
Thanks!
----------------------------------------------------------------------------------------
Alain Lavigne - Data Administrator - ZAQ Interactive Solutions E-Mail: alavigne(at)zaq(dot)com
297 St-Paul, West - Montreal, Quebec, Canada - H2Y 2A5
Phone: 514-282-7073 ext: 371 - Fax: 514-282-8011
From | Date | Subject | |
---|---|---|---|
Next Message | Chad Thompson | 2002-07-16 17:31:12 | Re: Seeding |
Previous Message | Devrim GUNDUZ | 2002-07-16 17:18:36 | A SQL Training |