> Is this particular query using xml features, and if so which? Actually,
> could you just show us the whole query and the schemas of the tables
> it's using?
No, other than returning a column of type xml. The query for the EXPLAIN
output I posted before is this:
select
lead.id as leadId,
lead.xml as xml,
lead.processing_state as processingState,
lead.processing_step as processingStep,
lead.processing_attempts as processingAttempts,
lead.created as createdDate,
lead.last_processed as lastProcessedDate
from lead lead
where
(date(lead.created at time zone interval '-06')
between date('2008-08-15') and date('2008-08-15'))
or
(date(lead.modified at time zone interval '-06')
between date('2008-08-15') and date('2008-08-15'))
order by lead.id
And the schema of the lead table is this:
lms_nna=# \d lead
Table "public.lead"
Column | Type | Modifiers
---------------------+--------------------------+-----------
id | bigint | not null
xml | xml |
source | character varying(50) | not null
destination | character varying(50) |
processing_state | character varying(20) | not null
created | timestamp with time zone | not null
modified | timestamp with time zone | not null
last_processed | timestamp with time zone |
processing_step | integer |
processing_attempts | integer |
Indexes:
"lead_pkey" PRIMARY KEY, btree (id), tablespace "lms_index"
"lead_created_idx" btree (created), tablespace "lms_index"
"lead_destination_idx" btree (destination), tablespace "lms_index"
"lead_modified_idx" btree (modified), tablespace "lms_index"
"lead_processing_step_idx" btree (processing_step), tablespace
"lms_index"
"lead_source_idx" btree (source), tablespace "lms_index"
"processing_state_idx" btree (processing_state), tablespace "lms_index"
"reporting_date_idx" btree (date(timezone('-06:00:00'::interval,
created))), tablespace "lms_index"
"reporting_last_processed_date_idx" btree
(date(timezone('-06:00:00'::interval, last_processed))), tablespace
"lms_index"
"reporting_modified_idx" btree (date(timezone('-06:00:00'::interval,
modified)))
Foreign-key constraints:
"fk_lead_destination" FOREIGN KEY (destination) REFERENCES
external_system(name) ON UPDATE RESTRICT ON DELETE RESTRICT
"fk_lead_source" FOREIGN KEY (source) REFERENCES external_system(name)
ON UPDATE RESTRICT ON DELETE RESTRICT
Triggers:
_lms_logtrigger_11 AFTER INSERT OR DELETE OR UPDATE ON lead FOR EACH
ROW EXECUTE PROCEDURE _lms.logtrigger('_lms', '11', 'kvvvvvvvvv')
create_lead_reporting_data AFTER INSERT OR UPDATE ON lead FOR EACH ROW
EXECUTE PROCEDURE update_lead_reporting_data()
set_modified BEFORE INSERT OR UPDATE ON lead FOR EACH ROW EXECUTE
PROCEDURE update_modified()
Would you also like the schemas for the referenced FOREIGN KEY tables? You
can see we have some insert/update triggers there, which I can provide the
source for if you need (update_lead_reporting_data() is quite large,
though).
-- m@
