Postgresql – Get a count of shippable orders

postgresql

I have two tables: orders and order_lines. I need a fast, inexpensive way to display a count for orders that are going to be shipping today.
The order_lines table is associated with the orders table view a foreign key based on order id. A line is shippable if the shipped field > 0.
An order is "shippable" if any of its lines have a qty of shipped.

Right now I basically have a query that reads through the orders, then through the lines (via sqlalchemy in python), and then if any of the lines can be shipped a counter is incremented. This works but it is very slow.

Is there some way on the database side or can create something to speed this up?

Best Answer

From your question I'd translate that you have the following table structure:

Orders:
    OrderId,
    OrderDetails

OrderLines:
    OrderLineId,
    OrderId,
    Shipped

I'd use the following statements for reading data:

-- shippable order lines
Select ol.*
from OrderLines ol
where ol.Shipped > 0

-- shippable orders
select *
from Order o
where Exists
(
    select *
    from OrderLines ol
    where ol.Shipped > 0
    and ol.OrderId = o.OrderId
)

In case the last script is again slow, than I'd guess that you need an index on the column OrderId in tabe OrderLines. In case this also doesn't work, then come back with the query that you used, the real tables definition and, most important, the EXPLAIN ANALYZE of your query.

PS: please keep in mind that I used the MS SQL flavor of the language, you might need small adjustments to make it work properly in PGSQL :-).

Subqueries to determine most common attributes

SELECT DISTINCT title
FROM   archived
WHERE  showurl = (
   SELECT showurl
   FROM   archived
   GROUP  BY 1
   ORDER  BY count(*) DESC, 1
   LIMIT  1
   )
AND    reference = (
   SELECT reference
   FROM   archived
   GROUP  BY 1
   ORDER  BY count(*) DESC, 1
   LIMIT  1
   );

I added DISTINCT to the outer SELECT to fold duplicate titles. It's undefined what you need exactly.

Shorter with window functions

A more modern / sophisticated / elegant approach would be with two window functions in the ORDER BY clause, allowing for a very terse query:

SELECT title
FROM   archived
ORDER  BY count(*) OVER (PARTITION BY showurl)   DESC
         ,count(*) OVER (PARTITION BY reference) DESC
LIMIT 1;

Not necessarily faster than the first solution, though. Again, guessing what you might want exactly, I only retrieve a single winning title.

The trick here is to partition the rows by showurl and reference, run a count over each partition and order descending by that count. This way, you can determine a winner in a single query level.

This will not work, if there is no row with the most common showurl and the most common reference at the same time. Again, exact requirements would be needed ...

-> SQLfiddle demo.

Best Answer

Related Solutions

Postgresql – Get count of processed queries

Postgresql – Retrieve title from rows with a maximum count in other columns

Subqueries to determine most common attributes

Shorter with window functions

Related Question