pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedperformance inconsistency
2+ messages / 2 participants
[nested] [flat]
* performance inconsistency
@ 2001-04-04 22:12 Phuong Ma <pma@commandprompt.com>
2001-04-04 22:47 ` Re: [SQL] performance inconsistency Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 2+ messages in thread
From: Phuong Ma @ 2001-04-04 22:12 UTC (permalink / raw)
To: pgsql-general@postgresql.org; pgsql-sql
Good day,
We're running a week-old CVS snapshot of PostgreSQL 7.1, and I'm not
sure
if this performance inconsistency is specific to it, or if this is just
something in PostgreSQL in general, but it seems kind of odd, and I
could
use some help here. ;)
I have run two queries in a table full of invoices where the only
difference
between the queries is the where clause:
SELECT COUNT(DISTINCT l."ORDER_NUM") AS line_orders,
SUM(l."GROSS_PROFIT" + "REPLACEMENT_COST") AS grand_total,
SUM(l."LIST_PRICE" * l."SHIPPED_QUANTITY") AS
grand_total_alternative,
SUM(l."GROSS_PROFIT") AS grand_total_profit
FROM cu_invoice_li1_tbl l INNER JOIN cu_invoice_03_tbl h
ON l."ORDER_NUM"=h."ORDER_NUM"
WHERE substr("ORDER_NUM", 1, 1) != 'W'; # (Orders NOT prefixed with W.)
OR...
WHERE substr("ORDER_NUM", 1, 1) = 'I'; # (Orders prefixed with I.)
The first query returns more rows than the second (because it includes
orders prefixed with I, C, F, etc), whereas the second only returns
orders
prefixed with I. It seems that the logic of identifying a single
character
as *not* being a single value would be identical or at least similar in
efficiency to the logic of a single character *being* being a single
value.
However, the first query runs in about 10-15 seconds, and the second
query
ran for over 40 minutes before I cancelled it.
Now, I can easily use a series of "!=" statements to get it down so that
it's only the 'I' orders (which runs even faster, at about 5-6
seconds!),
but can anyone explain this to me? :)
Are "!=" substring evaluations inherently faster by an obscene order of
magnitude, or is something really wrong here?
J.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: [SQL] performance inconsistency
2001-04-04 22:12 performance inconsistency Phuong Ma <pma@commandprompt.com>
@ 2001-04-04 22:47 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2001-04-04 22:47 UTC (permalink / raw)
To: Phuong Ma <pma@commandprompt.com>; +Cc: pgsql-general@postgresql.org; pgsql-sql
Phuong Ma <pma@commandprompt.com> writes:
> WHERE substr("ORDER_NUM", 1, 1) != 'W'; # (Orders NOT prefixed with W.)
> OR...
> WHERE substr("ORDER_NUM", 1, 1) = 'I'; # (Orders prefixed with I.)
> However, the first query runs in about 10-15 seconds, and the second
> query
> ran for over 40 minutes before I cancelled it.
What query plans does EXPLAIN show for each case? (And have you done
VACUUM ANALYZE lately?)
The change in the WHERE clause may be altering the planner's selectivity
guesstimates enough to cause choice of a less appropriate plan. Hard to
tell without seeing what EXPLAIN has to say, though.
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2001-04-04 22:47 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-04-04 22:12 performance inconsistency Phuong Ma <pma@commandprompt.com>
2001-04-04 22:47 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox