Query and first part of the table descriptions
Query:
explain analyze
select distinct on
(t8.id) t8.id,
t8.payment_id,
t8.amount_id,
t8.active,
t8.organization_id
from
project t2,
project_invoice t3,
payment t6,
invoice t7,
payment_amount t8,
amount t9,
department t10,
project_type t11
where
t10.company_id = 250893
and t2.department_id = t10.id
and t3.id = t7.owner_resource_id
and t2.id = t11.project_id
and t11.slot_id = t3.id
and t6.invoice_id = t7.id
and t6.id = t8.payment_id
and t8.amount_id = t9.id
and t2.active <> 0
and t3.active <> 0
and t6.active <> 0
and t7.active <> 0
and t10.active <> 0
and t11.active <> 0
and (t2.not_modified_since >= to_timestamp(E'2009-02-24T11:19:57', E'YYYY/MM/DD HH:MI:SS')
or t3.not_modified_since >= to_timestamp(E'2009-02-24T11:19:57', E'YYYY/MM/DD HH:MI:SS')
or t6.not_modified_since >= to_timestamp(E'2009-02-24T11:19:57', E'YYYY/MM/DD HH:MI:SS')
or t7.not_modified_since >= to_timestamp(E'2009-02-24T11:19:57', E'YYYY/MM/DD HH:MI:SS')
or t8.not_modified_since >= to_timestamp(E'2009-02-24T11:19:57', E'YYYY/MM/DD HH:MI:SS')
or t9.not_modified_since >= to_timestamp(E'2009-02-24T11:19:57', E'YYYY/MM/DD HH:MI:SS')
or t10.not_modified_since >= to_timestamp(E'2009-02-24T11:19:57', E'YYYY/MM/DD HH:MI:SS')
or t11.not_modified_since >= to_timestamp(E'2009-02-24T11:19:57', E'YYYY/MM/DD HH:MI:SS'));
Table "public.payment"
Column | Type | Modifiers
-----------------------+-----------------------------+--------------------
id | integer | not null
name | character varying(255) |
active | integer | not null default 1
status | numeric | not null default 0
last_status_change | date |
not_modified_since | timestamp without time zone |
organization_id | integer |
added_user_id | numeric |
added_tm | timestamp without time zone |
edited_user_id | numeric |
edited_tm | timestamp without time zone |
folder_id | integer |
max_duration_msec | numeric |
tax_id | integer |
customer_id | integer |
allow_custom_duration | numeric |
loop_tax_id | integer |
loop_weight | numeric |
fullbill | numeric |
invoice_id | integer |
trigger_tax_id | integer |
Indexes:
"payment_pkey" PRIMARY KEY, btree (id)
"idx_payment_organization_id" btree (organization_id)
"idx_payment_lower_name" btree (lower(name::text))
"idx_payment_nms" btree (not_modified_since)
"idx_payment_invoice_id" btree (invoice_id)
Foreign-key constraints:
"payment_tax_id_fkey" FOREIGN KEY (tax_id) REFERENCES tax(id)
"payment_folder_id_fkey" FOREIGN KEY (folder_id) REFERENCES folder(id)
"payment_customer_id_fkey" FOREIGN KEY (customer_id) REFERENCES customer(id)
"payment_loop_tax_id_fkey" FOREIGN KEY (loop_tax_id) REFERENCES tax(id)
"payment_invoice_id_fkey" FOREIGN KEY (invoice_id) REFERENCES invoice(id)
"payment_trigger_tax_id_fkey" FOREIGN KEY (trigger_tax_id) REFERENCES tax(id)
Inherits: resource
Table "public.payment_amount"
Column | Type | Modifiers
--------------------+-----------------------------+--------------------
id | integer | not null
name | character varying(255) |
active | integer | not null default 1
status | numeric | not null default 0
last_status_change | date |
not_modified_since | timestamp without time zone |
organization_id | integer |
added_user_id | numeric |
added_tm | timestamp without time zone |
edited_user_id | numeric |
edited_tm | timestamp without time zone |
folder_id | integer |
payment_id | integer | not null
amount_id | integer | not null
Indexes:
"payment_amount_pkey" PRIMARY KEY, btree (id)
"index_payment_amount" UNIQUE, btree (payment_id, amount_id)
"idx_payment_amount_payment_id" btree (payment_id)
"idx_payment_amount_organization_id" btree (organization_id)
"idx_payment_amount_lower_name" btree (lower(name::text))
"idx_payment_amount_nms" btree (not_modified_since)
Foreign-key constraints:
"payment_amount_payment_id_fkey" FOREIGN KEY (payment_id) REFERENCES payment(id)
"payment_amount_amount_id_fkey" FOREIGN KEY (amount_id) REFERENCES amount(id)
Inherits: resource
Table "public.amount"
Column | Type | Modifiers
---------------------+-----------------------------+--------------------
id | integer | not null
name | character varying(255) |
active | integer | not null default 1
status | numeric | not null default 0
last_status_change | date |
not_modified_since | timestamp without time zone |
organization_id | integer |
added_user_id | numeric |
added_tm | timestamp without time zone |
edited_user_id | numeric |
edited_tm | timestamp without time zone |
folder_id | integer |
description | character varying(255) |
originalfilename | character varying(255) |
mime | character varying(255) | not null
checksum | character varying(255) |
customer_id | integer |
approval_status | numeric |
checksum2_type | numeric |
checksum2 | character varying(255) |
size | numeric |
archive_status | numeric | default 0
archived_on_utc | timestamp without time zone |
archived_by | integer |
approved_by_user_id | integer |
approved_on_utc | timestamp without time zone |
external_id | character varying(255) |
archive_priority | smallint | not null
Indexes:
"amount_pkey" PRIMARY KEY, btree (id)
"idx_amount_organization_id" btree (organization_id)
"idx_amount_lower_name" btree (lower(name::text))
"idx_amount_nms" btree (not_modified_since)
"idx_amount_not_null_external_id" btree (external_id) WHERE external_id IS NOT NULL
Foreign-key constraints:
"amount_approved_by_user_id_fkey" FOREIGN KEY (approved_by_user_id) REFERENCES users(id)
"amount_archived_by_fkey" FOREIGN KEY (archived_by) REFERENCES server_node(id)
"amount_folder_id_fkey" FOREIGN KEY (folder_id) REFERENCES folder(id)
"amount_customer_id_fkey" FOREIGN KEY (customer_id) REFERENCES customer(id)
Inherits: resource
Table "public.department"
Column | Type | Modifiers
----------------------+-----------------------------+-----------------------------------
id | integer | not null
name | character varying(255) |
active | integer | not null default 1
status | numeric | not null default 0
last_status_change | date |
not_modified_since | timestamp without time zone |
organization_id | integer |
added_user_id | numeric |
added_tm | timestamp without time zone |
edited_user_id | numeric |
edited_tm | timestamp without time zone |
folder_id | integer |
day_mask | numeric |
virtual_start_date | date | not null
virtual_end_date | date | not null
start_date | date |
end_date | date |
start_time | time without time zone | not null
end_time | time without time zone | not null
weight | numeric |
company_id | integer | not null
impressions_per_hour | numeric | not null default (- (1)::numeric)
minute_mask | text |
Indexes:
"department_pkey" PRIMARY KEY, btree (id)
"idx_department_organization_id" btree (organization_id)
"idx_department_du" btree (company_id)
"idx_department_lower_name" btree (lower(name::text))
"idx_department_nms" btree (not_modified_since)
Foreign-key constraints:
"company_id_fkey" FOREIGN KEY (company_id) REFERENCES company(id)
Inherits: resource
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
