I am trying to CROSS JOIN two tables, customers and items, so I can then create a sales by customer by item report. I have 2000 customer and 2000 items.
SELECT customer_name FROM customers; --Takes 100ms
SELECT item_number FROM items; --Takes 50ms
SELECT customer_name, item_number FROM customers CROSS JOIN items; Takes 200000ms
I know this is 4 million rows, but is it possible to get this to run any faster? I want to eventually join this with a sales table like this:
SELECT customer_name, item_number, sales_total FROM customers CROSS JOIN items LEFT JOIN sales ON (customer.customer_name = sales.customer_name, item.item_number=sales.item_number);
The sales table will obviously not have all customers or all items, so the goal here is to have a report that shows all customers and all items along with what was sold and not sold.
I'm using PostgreSQL 8.4
To answer your question: No, you can't do a cross join faster than that - if you could then that would be how CROSS JOIN would be implemented.
But really you don't want a cross join. You probably want two separate queries, one which lists all customers, and another which lists all items and whether or not they were sold.