Here is a query I am trying to create. It gives me what I need but it takes way too long. Any ideas to make it faster...
I am trying to get the last name of each person that wrote a check on a certain date based on the date and the amount of money paid. Here is the query (there are three tables involved you can see the \d of the tables right beneath the query)
mydb=# explain analyze SELECT invoice.invoice_customer, invoice.invoice_date, invoice.invoice_number, customer.customer_lname, register_tape.register_checknumber FROM invoice LEFT JOIN customer ON invoice.invoice_customer = customer.customer_tie LEFT JOIN register_tape ON register_tape.register_invoice=invoice.invoice_number WHERE invoice.invoice_number=any(SELECT register_tape.register_invoice FROM register_tape WHERE register_tape.register_tape_date='01/02/2006' AND register_check='21.73') ORDER BY customer.customer_lname;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=1221568.75..1221568.76 rows=1 width=26) (actual time=239779.530..239779.534 rows=1 loops=1)
Sort Key: customer.customer_lname
-> Merge IN Join (cost=1147129.82..1221568.74 rows=1 width=26) (actual time=239779.393..239779.429 rows=1 loops=1)
Merge Cond: ("outer".invoice_number = "inner".register_invoice)
-> Merge Left Join (cost=1147079.95..1213063.22 rows=3441915 width=26) (actual time=198746.365..232869.981 rows=1744847 loops=1)
Merge Cond: ("outer".invoice_number = "inner".register_invoice)
-> Sort (cost=655105.86..663710.65 rows=3441915 width=22) (actual time=134526.000..141370.974 rows=1657937 loops=1)
Sort Key: invoice.invoice_number
-> Hash Left Join (cost=10443.51..237604.54 rows=3441915 width=22) (actual time=2566.125..85453.998 rows=3441877 loops=1)
Hash Cond: ("outer".invoice_customer = "inner".customer_tie)
-> Seq Scan on invoice (cost=0.00..105969.15 rows=3441915 width=12) (actual time=0.010..22366.220 rows=3441877 loops=1)
-> Hash (cost=8201.81..8201.81 rows=284681 width=14) (actual time=2565.794..2565.794 rows=0 loops=1)
-> Seq Scan on customer (cost=0.00..8201.81 rows=284681 width=14) (actual time=13.795..1356.293 rows=284682 loops=1)
-> Sort (cost=491974.09..500108.77 rows=3253873 width=8) (actual time=64220.318..70581.026 rows=1585007 loops=1)
Sort Key: public.register_tape.register_invoice
-> Seq Scan on register_tape (cost=0.00..111378.73 rows=3253873 width=8) (actual time=18.352..23442.059 rows=3253856 loops=1)
-> Sort (cost=49.87..49.88 rows=1 width=4) (actual time=0.736..0.740 rows=1 loops=1)
Sort Key: public.register_tape.register_invoice
-> Index Scan using register_tape_date_idx on register_tape (cost=0.00..49.86 rows=1 width=4) (actual time=0.526..0.701 rows=1 loops=1)
Index Cond: (register_tape_date = '2006-01-02'::date)
Filter: (register_check = 21.73)
Total runtime: 240089.863 ms
mydb=# \d invoice Table "public.invoice" Column | Type | Modifiers ---------------------+------------------------+-----------
invoice_date | date |
invoice_time | time without time zone |
invoice_seller | character varying(25) |
invoice_total | numeric(9,2) |
invoice_delivery | date |
invoice_payoff | date |
invoice_store | smallint |
invoice_number | integer | not null
invoice_checknumber | integer |
invoice_check | numeric(9,2) |
invoice_card | numeric(9,2) |
invoice_cash | numeric(9,2) |
invoice_credit | numeric(9,2) |
invoice_fico | numeric(9,2) |
invoice_subtotal | numeric(9,2) |
invoice_tax | numeric(9,2) |
invoice_commission | numeric(9,2) |
invoice_customer | integer |
Indexes:
"invoice_pkey" PRIMARY KEY, btree (invoice_number)
"invoice_customer_idx" btree (invoice_customer)
"invoice_date_idx" btree (invoice_date)
"invoice_delivery_idx" btree (invoice_delivery)
"invoice_store_idx" btree (invoice_store)
mydb=# \d register_tape
Table "public.register_tape"
Column | Type | Modifiers
----------------------+------------------------+---------------------------------------------------------------------------
register_card | numeric(9,2) |
register_checknumber | integer |
register_check | numeric(9,2) |
register_cash | numeric(9,2) |
register_fico | numeric(9,2) |
register_tape_number | smallint |
register_invoice | integer |
register_due | numeric(9,2) |
register_sub_tot | numeric(9,2) |
register_tax | numeric(9,2) |
register_total | numeric(9,2) |
register_pay | numeric(9,2) |
register_change | numeric(9,2) |
register_tape_date | date |
register_time | time without time zone |
register_order | integer | not null default nextval('public.register_tape_register_order_seq'::text)
register_store | smallint |
register_tax1 | numeric(9,2) |
register_tax2 | numeric(9,2) |
register_tax3 | numeric(9,2) |
Indexes:
"register_tape_date_idx" btree (register_tape_date)
"register_tape_num_date_idx" btree (register_tape_number, register_tape_date)
mydb=# \d customer
Table "public.customer"
Column | Type | Modifiers
---------------------+-----------------------+--------------------------------------------------------------------
customer_tie | integer | not null default nextval('public.customer_customer_tie_seq'::text)
customer_lname | character varying(50) |
customer_fname | character varying(50) |
customer_address | text |
customer_city | character varying(30) |
customer_state | character(2) |
customer_zip | character varying(5) |
customer_email | character varying(60) |
customer_phone | character varying(10) |
customer_workphone | character varying(10) |
customer_cellphone | character varying(10) |
customer_directions | text |
customer_type | smallint |
Indexes:
"customer_pkey" PRIMARY KEY, btree (customer_tie)
"customer_customer_phone_key" UNIQUE, btree (customer_phone)
"customer_name_idx" btree (customer_lname)
Thanks in advance
Panos
I am trying to get the last name of each person that wrote a check on a certain date based on the date and the amount of money paid. Here is the query (there are three tables involved you can see the \d of the tables right beneath the query)
mydb=# explain analyze SELECT invoice.invoice_customer, invoice.invoice_date, invoice.invoice_number, customer.customer_lname, register_tape.register_checknumber FROM invoice LEFT JOIN customer ON invoice.invoice_customer = customer.customer_tie LEFT JOIN register_tape ON register_tape.register_invoice=invoice.invoice_number WHERE invoice.invoice_number=any(SELECT register_tape.register_invoice FROM register_tape WHERE register_tape.register_tape_date='01/02/2006' AND register_check='21.73') ORDER BY customer.customer_lname;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=1221568.75..1221568.76 rows=1 width=26) (actual time=239779.530..239779.534 rows=1 loops=1)
Sort Key: customer.customer_lname
-> Merge IN Join (cost=1147129.82..1221568.74 rows=1 width=26) (actual time=239779.393..239779.429 rows=1 loops=1)
Merge Cond: ("outer".invoice_number = "inner".register_invoice)
-> Merge Left Join (cost=1147079.95..1213063.22 rows=3441915 width=26) (actual time=198746.365..232869.981 rows=1744847 loops=1)
Merge Cond: ("outer".invoice_number = "inner".register_invoice)
-> Sort (cost=655105.86..663710.65 rows=3441915 width=22) (actual time=134526.000..141370.974 rows=1657937 loops=1)
Sort Key: invoice.invoice_number
-> Hash Left Join (cost=10443.51..237604.54 rows=3441915 width=22) (actual time=2566.125..85453.998 rows=3441877 loops=1)
Hash Cond: ("outer".invoice_customer = "inner".customer_tie)
-> Seq Scan on invoice (cost=0.00..105969.15 rows=3441915 width=12) (actual time=0.010..22366.220 rows=3441877 loops=1)
-> Hash (cost=8201.81..8201.81 rows=284681 width=14) (actual time=2565.794..2565.794 rows=0 loops=1)
-> Seq Scan on customer (cost=0.00..8201.81 rows=284681 width=14) (actual time=13.795..1356.293 rows=284682 loops=1)
-> Sort (cost=491974.09..500108.77 rows=3253873 width=8) (actual time=64220.318..70581.026 rows=1585007 loops=1)
Sort Key: public.register_tape.register_invoice
-> Seq Scan on register_tape (cost=0.00..111378.73 rows=3253873 width=8) (actual time=18.352..23442.059 rows=3253856 loops=1)
-> Sort (cost=49.87..49.88 rows=1 width=4) (actual time=0.736..0.740 rows=1 loops=1)
Sort Key: public.register_tape.register_invoice
-> Index Scan using register_tape_date_idx on register_tape (cost=0.00..49.86 rows=1 width=4) (actual time=0.526..0.701 rows=1 loops=1)
Index Cond: (register_tape_date = '2006-01-02'::date)
Filter: (register_check = 21.73)
Total runtime: 240089.863 ms
mydb=# \d invoice Table "public.invoice" Column | Type | Modifiers ---------------------+------------------------+-----------
invoice_date | date |
invoice_time | time without time zone |
invoice_seller | character varying(25) |
invoice_total | numeric(9,2) |
invoice_delivery | date |
invoice_payoff | date |
invoice_store | smallint |
invoice_number | integer | not null
invoice_checknumber | integer |
invoice_check | numeric(9,2) |
invoice_card | numeric(9,2) |
invoice_cash | numeric(9,2) |
invoice_credit | numeric(9,2) |
invoice_fico | numeric(9,2) |
invoice_subtotal | numeric(9,2) |
invoice_tax | numeric(9,2) |
invoice_commission | numeric(9,2) |
invoice_customer | integer |
Indexes:
"invoice_pkey" PRIMARY KEY, btree (invoice_number)
"invoice_customer_idx" btree (invoice_customer)
"invoice_date_idx" btree (invoice_date)
"invoice_delivery_idx" btree (invoice_delivery)
"invoice_store_idx" btree (invoice_store)
mydb=# \d register_tape
Table "public.register_tape"
Column | Type | Modifiers
----------------------+------------------------+---------------------------------------------------------------------------
register_card | numeric(9,2) |
register_checknumber | integer |
register_check | numeric(9,2) |
register_cash | numeric(9,2) |
register_fico | numeric(9,2) |
register_tape_number | smallint |
register_invoice | integer |
register_due | numeric(9,2) |
register_sub_tot | numeric(9,2) |
register_tax | numeric(9,2) |
register_total | numeric(9,2) |
register_pay | numeric(9,2) |
register_change | numeric(9,2) |
register_tape_date | date |
register_time | time without time zone |
register_order | integer | not null default nextval('public.register_tape_register_order_seq'::text)
register_store | smallint |
register_tax1 | numeric(9,2) |
register_tax2 | numeric(9,2) |
register_tax3 | numeric(9,2) |
Indexes:
"register_tape_date_idx" btree (register_tape_date)
"register_tape_num_date_idx" btree (register_tape_number, register_tape_date)
mydb=# \d customer
Table "public.customer"
Column | Type | Modifiers
---------------------+-----------------------+--------------------------------------------------------------------
customer_tie | integer | not null default nextval('public.customer_customer_tie_seq'::text)
customer_lname | character varying(50) |
customer_fname | character varying(50) |
customer_address | text |
customer_city | character varying(30) |
customer_state | character(2) |
customer_zip | character varying(5) |
customer_email | character varying(60) |
customer_phone | character varying(10) |
customer_workphone | character varying(10) |
customer_cellphone | character varying(10) |
customer_directions | text |
customer_type | smallint |
Indexes:
"customer_pkey" PRIMARY KEY, btree (customer_tie)
"customer_customer_phone_key" UNIQUE, btree (customer_phone)
"customer_name_idx" btree (customer_lname)
Thanks in advance
Panos