[SQL] Inconsistent usage of Index
Hi All, I have attached file explaining inconsistent usage of Index. Thanks, Subra __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.mail.yahoo.com/ --- Table "valid_testtypes" Attribute | Type | Modifier ---+--+-- v_ttyp_code | varchar(100) | not null v_ttyp_description| varchar(50) | not null v_ttyp_classification | varchar(15) | not null v_ttyp_status | char(1) | not null v_ttyp_status_date| timestamp| not null v_ttyp_user_created | varchar(30) | not null v_ttyp_date_created | timestamp| not null v_ttyp_user_modified | varchar(30) | v_ttyp_date_modified | timestamp| v_ttyp_reptype| varchar(10) | not null Index: valid_testtypes_pkey select v_ttyp_code from valid_testtypes ; v_ttyp_code XRAY SCAN ECG PT CTG Bio-Chemistry Biopsy Clinical Pathology Complete Haematogram Endocrinology Haematology Table "allied_medical_req_main" Attribute | Type | Modifier ---+--+-- amr_sequence_num | bigint | not null amr_id| bigint | not null pat_reg_no| bigint | not null visit_id | bigint | emp_referral | varchar(30) | v_eref_id | bigint | amr_date_created | timestamp| not null amr_user_created | varchar(30) | not null amr_date_modified | timestamp| amr_user_modified | varchar(30) | amr_complete_flag | char(1) | not null amr_report_req_flag | char(1) | amr_film_req_flag | char(1) | amr_request_date | varchar(10) | not null amr_request_time | varchar(12) | not null amr_remarks | varchar(200) | amr_report_to_be_collected_on | timestamp| not null amr_report_collected_on | timestamp| v_ttyp_code | varchar(30) | not null Indices: allied_medical_req_main_pkey, ind_amr_v_ttyp_code, uk_amr_id_v_ttyp_code Instance #1 --- explain select * from allied_medical_req_main where v_ttyp_code = 'XRAY' mnh_hospdb-# ; NOTICE: QUERY PLAN: Index Scan using ind_amr_v_ttyp_code on allied_medical_req_main (cost=0.00..20.35 row s=22 width=192) EXPLAIN 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 00 -- 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. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Integrity and Inheritance
I wanted to build the following schema : - one "generic" document table with a column doc_id ; - a couple of "specific" document tables inheriting from doc ; - a table refering a document by it's id with and integrity constraint on it. In SQL : CREATE TABLE doc ( doc_id serial PRIMARY KEY, ); CREATE TABLE lexique ( ) INHERITS (doc); CREATE TABLE word_doc ( id serial PRIMARY KEY, doc_id int4 NOT NULL CONSTRAINT word_doc_doc_id_ref REFERENCES doc ON DELETE CASCADE, ); What I tried to do next is to insert a new "lexique" entry, and then a bunch of "word_doc" rows refering this entry. The last part fails with a constraint violation this is quite normal since the trigger in backend/utils/adt/ri_triggers.c makes a "SELECT FROM ONLY". What should I do ? Should I consider another way to do what I want, rewrite a trigger to replace the system one ? -- Le cinéma en Lumière : http://www.lumiere.org/ Fingerprint : 4721 651D D309 B302 93E4 5D73 CC52 DF3A E7C7 86CF ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Integrity and Inheritance
On 10 Jun 2001, Christophe Labouisse wrote: > I wanted to build the following schema : > > - one "generic" document table with a column doc_id ; > - a couple of "specific" document tables inheriting from doc ; > - a table refering a document by it's id with and integrity constraint > on it. > > In SQL : > > CREATE TABLE doc ( > doc_id serial PRIMARY KEY, > ); > > CREATE TABLE lexique ( > ) INHERITS (doc); > > CREATE TABLE word_doc ( > id serial PRIMARY KEY, > doc_id int4 NOT NULL CONSTRAINT word_doc_doc_id_ref REFERENCES doc ON DELETE >CASCADE, > ); > > > What I tried to do next is to insert a new "lexique" entry, and then a > bunch of "word_doc" rows refering this entry. The last part fails with > a constraint violation this is quite normal since the trigger in > backend/utils/adt/ri_triggers.c makes a "SELECT FROM ONLY". > > What should I do ? Should I consider another way to do what I want, > rewrite a trigger to replace the system one ? One problem is that for update isn't supported across inheritance trees AFAICS and the triggers use for update for the appropriate locking. If you were to write a trigger that worked across the tree, you'd probably need to take that into account for the locking. You might be best off not using inheritance, and giving the "subtables" ids that refer back to a separate doc_id table that keeps the ids. This has the downside of requiring a separate write for the doc_id and document tables, but means that you don't hit any of the wierdness surrounding inheritance. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
