Stephen Richards wrote:
> I don't know if this is what you were looking for, but here it is:
It is.
> The thing is, I know there is plenty I can do to make these queries run
> faster - it is just a general question: why does CF just hang for all the
> other users when this query is executed from a CF page, and not when
> executed from the db itself?
I don't know.
> You sound as though you
> have experience of CF with Postgres on a Lunux box?
CF on Windows and PostgreSQL on OpenBSD and Windows.
> EXPLAIN ANALYZE
> SELECT count(*) as count,
> ROUND(sum(order_value(h.orderid))/100.0,2) as value
> FROM statusstats3 h
> INNER JOIN orders o ON (o.orderid = h.orderid)
> WHERE h.time::date BETWEEN '2002-10-01' AND '2002-10-31'
> AND h.tostatus = 'sales_approved'
> AND o.status != 'order_cancelled'
> AND o.existing = false
>
> NOTICE: QUERY PLAN:
>
> Aggregate (cost=5459.05..5459.05 rows=1 width=8) (actual
> time=52296.17..52296.17 rows=1 loops=1)
> -> Nested Loop (cost=5453.01..5459.04 rows=1 width=8) (actual
> time=39563.71..39704.95 rows=571 loops=1)
> -> Subquery Scan h (cost=5453.01..5453.02 rows=1 width=36) (actual
> time=39563.47..39574.80 rows=742 loops=1)
> -> Unique (cost=5453.01..5453.02 rows=1 width=36) (actual
> time=39563.46..39570.71 rows=742 loops=1)
> -> Sort (cost=5453.01..5453.01 rows=1 width=36)
> (actual
> time=39563.46..39565.25 rows=849 loops=1)
> -> Seq Scan on orderhistory (cost=0.00..5453.00
> rows=1 width=36) (actual time=9998.10..39559.34 rows=849 loops=1)
This seqscan is taking 40 seconds out of a total 53, so it is the
obvious candidate for improvement.
> -> Index Scan using orders_pkey on orders o (cost=0.00..6.01
> rows=1
> width=4) (actual time=0.15..0.16 rows=1 loops=742)
> Total runtime: 52296.58 msec
>
> messina=# select count(*) from orders;
> count
> -------
> 4914
> (1 row)
>
> messina=# select count(*) from orderatoms;
> count
> -------
> 9222
> (1 row)
>
> messina=# select count(*) from statusstats3;
> count
> -------
> 21452
> (1 row)
>
>
> Statusstats3 is a view of the orderhistory table, hence the reference to
> orderhistory in the explain above.
How many rows are there in orderhistory?
> messina=# \d statusstats3
> View "statusstats3"
> Column | Type | Modifiers
> ----------+-----------------------------+-----------
> orderid | integer |
> tostatus | character varying |
> time | timestamp without time zone |
> View definition: SELECT DISTINCT orderhistory.orderid,
> get_status((orderhistory.changes)::"varchar") AS tostatus,
> min_status_time(orderhistory.orderid,
> get_status((orderhistory.changes)::"varchar"))
> AS "time" FROM orderhistory WHERE ((orderhistory.tablename =
> 'orders'::"varchar") AND (orderhistory.changes ~~ '%ST:%,%'::text)) ORDER BY
> orderhistory.orderid, get_status((orderhistory.changes)::"varchar"),
> min_status_time(orderhistory.orderid,
> get_status((orderhistory.changes)::"varchar"));
I am betting that it is the LIKE that is killing you here, it is pretty
much impossible to index one using a normal index. Which is why you need
a partial index on the predefined search pattern:
CREATE INDEX test_idx ON orderhistory USING HASH (changes)
WHERE (changes ~~ '%ST:%,%'::text);
Whether it works depends on the cardinality of the table and the
expected resultset. Just remember that you need to create partial
indexes for each query pattern.
Jochem
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Signup for the Fusion Authority news alert and keep up with the latest news in
ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
