Thread: Need help on a troublesome query plan
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 nullbus_fact_ts | timestamp with time zone | not nullparty_id | bigint | svc_id | bigint | not nullbus_fact_data | text | not nullbus_fact_typ_cd| character(10) | not nullbus_fact_kywrd | character varying(300) | cont_id | bigint | perfby_id | bigint | Index "bld_x1_tb_bus_fact" Column | Type -----------------+---------------party_id | bigintbus_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@zaq.com 297 St-Paul, West - Montreal, Quebec, Canada - H2Y 2A5 Phone: 514-282-7073 ext: 371 - Fax: 514-282-8011
On Tue, 16 Jul 2002, Alain Lavigne wrote: > 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; You'll need to either single quote or explicitly cast the constants you're comparing to the bigint columns.
On Tue, 16 Jul 2002, Alain Lavigne wrote: > Thanks that worked, but why does that happen or maybe you could point > to the proper thread so I read up on it. It's an issue that the numeric constant gets cast to int4 somewhat prematurely. I don't have a pointer to a thread off hand, but if you search the archives you should find a couple.
Thanks that worked, but why does that happen or maybe you could point to the proper thread so I read up on it. ---------------------------------------------------------------------------------------- Alain Lavigne - Data Administrator - ZAQ Interactive Solutions E-Mail: alavigne@zaq.com 297 St-Paul, West - Montreal, Quebec, Canada - H2Y 2A5 Phone: 514-282-7073 ext: 371 - Fax: 514-282-8011 -----Original Message----- From: Stephan Szabo [mailto:sszabo@megazone23.bigpanda.com] Sent: July 16, 2002 14:52 To: Alain Lavigne Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] Need help on a troublesome query plan On Tue, 16 Jul 2002, Alain Lavigne wrote: > 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; You'll need to either single quote or explicitly cast the constants you're comparing to the bigint columns.