pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedQuery Plan
4+ messages / 4 participants
[nested] [flat]
* Query Plan
@ 2004-09-09 05:30 Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>
2004-09-09 08:28 ` Re: Query Plan Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 4+ messages in thread
From: Theo Galanakis @ 2004-09-09 05:30 UTC (permalink / raw)
To: pgsql-sql
Have a question regarding when a Query Plan uses an Index.
I have a basic statement
This query uses the Index Scan:
explain analyse select * from tablea where columna_id < 57
This query uses Seq Scan:
explain analyse select * from tablea where columna_id < 58
There are about 5000 records in this table, and columna_id is the primary
key, which is Indexed... And recently recreated.
My question is why is the Plan not using the index on the second occasions?
Theo
______________________________________________________________________
This email, including attachments, is intended only for the addressee
and may be confidential, privileged and subject to copyright. If you
have received this email in error, please advise the sender and delete
it. If you are not the intended recipient of this email, you must not
use, copy or disclose its content to anyone. You must not copy or
communicate to others content that is confidential or subject to
copyright, unless you have the consent of the content owner.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Query Plan
2004-09-09 05:30 Query Plan Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>
@ 2004-09-09 08:28 ` Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 4+ messages in thread
From: Richard Huxton @ 2004-09-09 08:28 UTC (permalink / raw)
To: Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>; +Cc: pgsql-sql
Theo Galanakis wrote:
>
> Have a question regarding when a Query Plan uses an Index.
>
> I have a basic statement
>
> This query uses the Index Scan:
> explain analyse select * from tablea where columna_id < 57
>
> This query uses Seq Scan:
> explain analyse select * from tablea where columna_id < 58
>
> There are about 5000 records in this table, and columna_id is the primary
> key, which is Indexed... And recently recreated.
OK - what does explain analyse say?
Each line will have two parts, the expected costs/number of rows and the
actual time/number of rows.
If PG is expecting say 50 rows in the first query but 150 in the second
then it might decide a sequential scan is quicker.
Post the outputs of explain analyse and we can compare them.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 4+ messages in thread
* Query Plan
@ 2007-07-12 21:11 Radhika Sambamurti <radhika@88thstreet.com>
2007-07-12 22:14 ` Re: Query Plan Gregory Stark <stark@enterprisedb.com>
0 siblings, 1 reply; 4+ messages in thread
From: Radhika Sambamurti @ 2007-07-12 21:11 UTC (permalink / raw)
To: pgsql-sql
Hi,
I have a complicated view joining 3 tables.
Here are the run times:
LOG: duration: 3380.672 ms statement: explain analyze SELECT * from
vtradeblock where FirmClearingid = 'FIRMA' and status = 1;
LOG: duration: 3784.152 ms statement: explain analyze SELECT * from
vtradeblock where date_trunc('day',tradedate) = '20070703';
LOG: duration: 19631.958 ms statement: EXPLAIN ANALYZE SELECT * from
vtradeblock where FirmClearingid = 'ALLIANCE' and status = 1 and
date_trunc('day', tradedate) = '20070703';
When I run the query with combination of FirmClearingID & status the run
times are approx 3700ms.
But when I add tradedate ie date_trunc('day', tradedate) = '20070703' the
run time becomes a horrendous 19631.958 ms.
I am displaying the query plan from Explain Analyze:
Query 1 (without date_trunc)
Hash Left Join (cost=103783.42..104890.34 rows=154 width=992) (actual
time=3744.962..3781.749 rows=149 loops=1)
Hash Cond: (t.blockid = a.blockid)
-> Nested Loop (cost=17359.18..18450.32 rows=154 width=912) (actual
time=840.575..842.620 rows=149 loops=1)
-> HashAggregate (cost=17359.18..17366.50 rows=154 width=356)
(actual time=840.489..841.018 rows=149 loops=1)
-> Seq Scan on tradetbl (cost=0.00..17309.26 rows=1536
width=356) (actual time=828.272..838.045 rows=406 loops=1)
Filter: (date_trunc('day'::text,
(date_trunc('day'::text, exectime) +
'12:00:00'::interval)) = '2007-07-03
00:00:00'::timestamp without time zone)
-> Index Scan using tradeblocktbl_pkey on tradeblocktbl tr
(cost=0.00..7.02 rows=1 width=618) (actual time=0.006..0.007
rows=1 loops=149)
Index Cond: (t.blockid = tr.recid)
-> Hash (cost=86423.74..86423.74 rows=200 width=84) (actual
time=2873.368..2873.368 rows=118694 loops=1)
-> Subquery Scan a (cost=0.00..86423.74 rows=200 width=84)
(actual time=0.363..2643.006 rows=118694 loops=1)
-> GroupAggregate (cost=0.00..86421.74 rows=200 width=32)
(actual time=0.359..2461.812 rows=118694 loops=1)
-> GroupAggregate (cost=0.00..85417.24 rows=40000
width=12) (actual time=0.279..1825.833 rows=118696
loops=1)
-> Index Scan using k_alloctbl_blockid_status
on alloctbl (cost=0.00..73516.91 rows=588590
width=12) (actual time=0.069..871.672
rows=588590 loops=1)
Total runtime: 3782.349 ms
(14 rows)
Query Plan 2 (with date trunc)
Nested Loop Left Join (cost=4269.91..90708.79 rows=1 width=992) (actual
time=2455.184..19629.407 rows=8 loops=1)
Join Filter: (t.blockid = a.blockid)
Filter: (CASE WHEN ((a.qty_ready = 0) AND (a.qty_submitted = 0)) THEN 0
WHEN ((a.qty_ready = 0) AND (a.qty_submitted = t.netshares)) THEN 1
WHEN (a.qty_ready > 0) THEN 2 WHEN ((a.qty_submitted > 0) AND
(a.qty_ready = 0) AND (a.qty_submitted < t.netshares)) THEN 3 ELSE 0
END = 1)
-> Nested Loop (cost=4269.91..4278.01 rows=1 width=912) (actual
time=28.149..28.674 rows=8 loops=1)
-> HashAggregate (cost=4269.91..4269.96 rows=1 width=356)
(actual time=28.073..28.151 rows=8 loops=1)
-> Bitmap Heap Scan on tradetbl (cost=31.56..4269.65
rows=8 width=356) (actual time=27.193..27.878 rows=20
loops=1)
Recheck Cond: ((firmclearingid)::text = 'FIRMA'::text)
Filter: (date_trunc('day'::text,
(date_trunc('day'::text, exectime) +
'12:00:00'::interval)) = '2007-07-03
00:00:00'::timestamp without time zone)
-> Bitmap Index Scan on k_tradetbl_firmclearingid
(cost=0.00..31.56 rows=1536 width=0) (actual
time=1.201..1.201 rows=2643 loops=1)
Index Cond: ((firmclearingid)::text =
'FIRMA'::text)
-> Index Scan using tradeblocktbl_pkey on tradeblocktbl tr
(cost=0.00..8.03 rows=1 width=618) (actual time=0.052..0.055
rows=1 loops=8)
Index Cond: (t.blockid = tr.recid)
-> GroupAggregate (cost=0.00..86421.74 rows=200 width=32) (actual
time=0.300..2363.811 rows=118694 loops=8)
-> GroupAggregate (cost=0.00..85417.24 rows=40000 width=12)
(actual time=0.244..1766.637 rows=118696 loops=8)
-> Index Scan using k_alloctbl_blockid_status on alloctbl
(cost=0.00..73516.91 rows=588590 width=12) (actual
time=0.060..854.834 rows=588590 loops=8)
Total runtime: 19629.777 ms
(16 rows)
The Trade table is being indexed by symbol, side, firmclearingid.
Even when i add an index to the date, ie date_trunc, it is not helping.
Any suggestions?
Thanks,
Radhika
--
It is all a matter of perspective. You choose your view by choosing where
to stand.
Larry Wall
---
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Query Plan
2007-07-12 21:11 Query Plan Radhika Sambamurti <radhika@88thstreet.com>
@ 2007-07-12 22:14 ` Gregory Stark <stark@enterprisedb.com>
0 siblings, 0 replies; 4+ messages in thread
From: Gregory Stark @ 2007-07-12 22:14 UTC (permalink / raw)
To: radhika@88thstreet.com; +Cc: pgsql-sql
"Radhika Sambamurti" <radhika@88thstreet.com> writes:
> When I run the query with combination of FirmClearingID & status the run
> times are approx 3700ms.
> But when I add tradedate ie date_trunc('day', tradedate) = '20070703' the
> run time becomes a horrendous 19631.958 ms.
I'm not really able to make heads or tails of your plans without the query.
But any where clause of the form date_trunc('...',col)='...' will always
generate crappy plans. And it looks like you already have expressions of that
form in the view even in the one which you label "without date_trunc".
Instead try to write a where clause that the database can understand the end
points of. Something like (col >= '20070703' and col < '20070704'). That's
something the database can apply a normal index to and also something it can
have a chance at guessing how many rows will fit.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2007-07-12 22:14 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-09-09 05:30 Query Plan Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>
2004-09-09 08:28 ` Richard Huxton <dev@archonet.com>
2007-07-12 21:11 Query Plan Radhika Sambamurti <radhika@88thstreet.com>
2007-07-12 22:14 ` Gregory Stark <stark@enterprisedb.com>
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