SEO

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi all,
I'm using 8.2.6 and I'm observing a trange behaviour using
offset and limits.

This are the two queries that are puzzling me:

explain SELECT c.id, tsk, lir, nctr, nctn, ncts, rvel,ecp, pvcp, pvcc,pvcf,pvcl,ldcn,ogtd,sgti
FROM t_OA_2_00_card c JOIN t_OA_2_00_dt dt ON (dt.card_id = c.id)
WHERE ecp=18 AND _to >= 1500 AND _from <= 1550
ORDER BY nctr,nctn,ncts,rvel
offset 0 ;
QUERY PLAN
- ----------------------------------------------------------------------------------------------------------------
Limit (cost=175044.75..175071.04 rows=10518 width=90)
-> Sort (cost=175044.75..175071.04 rows=10518 width=90)
Sort Key: c.nctr, c.nctn, c.ncts, c.rvel
-> Hash Join (cost=25830.72..174342.12 rows=10518 width=90)
Hash Cond: (c.id = dt.card_id)
-> Bitmap Heap Scan on t_oa_2_00_card c (cost=942.36..148457.19 rows=101872 width=90)
Recheck Cond: (ecp = 18)
-> Bitmap Index Scan on i7_t_oa_2_00_card (cost=0.00..916.89 rows=101872 width=0)
Index Cond: (ecp = 18)
-> Hash (cost=22743.45..22743.45 rows=171593 width=8)
-> Bitmap Heap Scan on t_oa_2_00_dt dt (cost=2877.26..22743.45 rows=171593 width=8)
Recheck Cond: (_from <= 1550)
Filter: (_to >= 1500)
-> Bitmap Index Scan on i_oa_2_00_dt_from (cost=0.00..2834.36 rows=182546 width=0)
Index Cond: (_from <= 1550)


explain SELECT c.id, tsk, lir, nctr, nctn, ncts, rvel,ecp, pvcp, pvcc,pvcf,pvcl,ldcn,ogtd,sgti
FROM t_OA_2_00_card c JOIN t_OA_2_00_dt dt ON (dt.card_id = c.id)
WHERE ecp=18 AND _to >= 1500 AND _from <= 1550
ORDER BY nctr,nctn,ncts,rvel
offset 0 limit 5;
QUERY PLAN
- --------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..2125.12 rows=5 width=90)
-> Nested Loop (cost=0.00..4470402.02 rows=10518 width=90)
-> Index Scan using i_oa_2_00_card_keys on t_oa_2_00_card c (cost=0.00..3927779.56 rows=101872 width=90)
Filter: (ecp = 18)
-> Index Scan using i_oa_2_00_dt_for on t_oa_2_00_dt dt (cost=0.00..5.31 rows=1 width=8)
Index Cond: (dt.card_id = c.id)
Filter: ((_to >= 1500) AND (_from <= 1550))


using the limit I have an execution time of minutes vs a some seconds.

What am I missing here ?

Regards
Gaetano Mendola



-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFHov3I7UpzwH2SGd4RApR+AJ0dG/+0MoB3PMD1kRgQt0BisHwQBACgzVwC
BN/SBWrvVxVE9eBLK0C1Pnw=
=9Ucp
-----END PGP SIGNATURE-----

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Martijn van Oosterhout wrote:
> On Fri, Feb 01, 2008 at 12:08:56PM +0100, Gaetano Mendola wrote:
>> -----BEGIN PGP SIGNED MESSAGE-----
>> Hash: SHA1
>>
>> Hi all,
>> I'm using 8.2.6 and I'm observing a trange behaviour using
>> offset and limits.
>
> Please post EXPLAIN ANALYZE output so we can see what's actually taking
> the time.

The analyze is still running (I launched it 30 mins ago), I'll post it as soon
I have it.

Disabling the nested_loop ( set enable_nestloop = false ) the query with the limit
has now the same execution time without the limit.

I don't get why a limit is going to change the query plan and most of all decreasing
the performances.


Regards
Gaetano Mendola
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFHowXA7UpzwH2SGd4RAomqAJ409579Jk7d5FYWf92PjO YDRxWNIQCggg1w
1WJcVmn2g1MASBGh9OtCQ0Q=
=h2Z6
-----END PGP SIGNATURE-----

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

"Gaetano Mendola" <mendola@bigfoot.com> writes:

> I don't get why a limit is going to change the query plan and most of all decreasing
> the performances.

Until we see the explain analyze it won't be clear what exactly is going on.
But in theory a LIMIT can definitely change the plan because the planner knows
it won't need to generate all the rows to satisfy the LIMIT.

In the plans you gave note that the plan for the unlimited query has a Sort so
it has to produce all the records every time. The second query produces the
records in order so if the LIMIT is satisfied quickly then it can save a lot
of work.

It's evidently guessing wrong about the limit being satisfied early. The
non-indexed restrictions might be pruning out a lot more records than the
planner expects. Or possibly the table is just full of dead records.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's PostGIS support!

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Gregory Stark wrote:
> "Gaetano Mendola" <mendola@bigfoot.com> writes:
>
>> I don't get why a limit is going to change the query plan and most of all decreasing
>> the performances.
>
> Until we see the explain analyze it won't be clear what exactly is going on.
> But in theory a LIMIT can definitely change the plan because the planner knows
> it won't need to generate all the rows to satisfy the LIMIT.
>
> In the plans you gave note that the plan for the unlimited query has a Sort so
> it has to produce all the records every time. The second query produces the
> records in order so if the LIMIT is satisfied quickly then it can save a lot
> of work.
>
> It's evidently guessing wrong about the limit being satisfied early. The
> non-indexed restrictions might be pruning out a lot more records than the
> planner expects. Or possibly the table is just full of dead records.
>

Here the analyze result:


explain analyze SELECT c.id, tsk, lir, nctr, nctn, ncts, rvel,ecp, pvcp, pvcc,pvcf,pvcl,ldcn,ogtd,sgti FROM t_OA_2_00_card c JOIN t_OA_2_00_dt dt ON (dt.card_id = c.id) WHERE ecp=18 AND _to >= 1500 AND _from <= 1550 ORDER BY nctr,nctn,ncts,rvel offset 0 limit 5;

QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..2125.12 rows=5 width=90) (actual time=3399923.424..3399960.174 rows=5 loops=1)
-> Nested Loop (cost=0.00..4470402.02 rows=10518 width=90) (actual time=3399923.420..3399960.156 rows=5 loops=1)
-> Index Scan using i_oa_2_00_card_keys on t_oa_2_00_card c (cost=0.00..3927779.56 rows=101872 width=90) (actual time=3399892.632..3399896.773 rows=50 loops=1)
Filter: (ecp = 18)
-> Index Scan using i_oa_2_00_dt_for on t_oa_2_00_dt dt (cost=0.00..5.31 rows=1 width=8) (actual time=1.264..1.264 rows=0 loops=50)
Index Cond: (dt.card_id = c.id)
Filter: ((_to >= 1500) AND (_from <= 1550))
Total runtime: 3399960.277 ms


explain analyze SELECT c.id, tsk, lir, nctr, nctn, ncts, rvel,ecp, pvcp, pvcc,pvcf,pvcl,ldcn,ogtd,sgti FROM t_OA_2_00_card c JOIN t_OA_2_00_dt dt ON (dt.card_id = c.id) WHERE ecp=18 AND _to >= 1500 AND _from <= 1550 ORDER BY nctr,nctn,ncts,rvel offset 0 ;
QUERY PLAN -----------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=175044.75..175071.04 rows=10518 width=90) (actual time=2425.138..2435.633 rows=3298 loops=1)
-> Sort (cost=175044.75..175071.04 rows=10518 width=90) (actual time=2425.134..2428.812 rows=3298 loops=1)
Sort Key: c.nctr, c.nctn, c.ncts, c.rvel
-> Hash Join (cost=25830.72..174342.12 rows=10518 width=90) (actual time=797.540..2382.900 rows=3298 loops=1)
Hash Cond: (c.id = dt.card_id)
-> Bitmap Heap Scan on t_oa_2_00_card c (cost=942.36..148457.19 rows=101872 width=90) (actual time=70.212..1507.429 rows=97883 loops=1)
Recheck Cond: (ecp = 18)
-> Bitmap Index Scan on i7_t_oa_2_00_card (cost=0.00..916.89 rows=101872 width=0) (actual time=53.340..53.340 rows=97883 loops=1)
Index Cond: (ecp = 18)
-> Hash (cost=22743.45..22743.45 rows=171593 width=8) (actual time=726.597..726.597 rows=89277 loops=1)
-> Bitmap Heap Scan on t_oa_2_00_dt dt (cost=2877.26..22743.45 rows=171593 width=8) (actual time=86.181..593.275 rows=89277 loops=1)
Recheck Cond: (_from <= 1550)
Filter: (_to >= 1500)
-> Bitmap Index Scan on i_oa_2_00_dt_from (cost=0.00..2834.36 rows=182546 width=0) (actual time=80.863..80.863 rows=201177 loops=1)
Index Cond: (_from <= 1550)
Total runtime: 2440.396 ms



Regards
Gaetano Mendola
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFHoytQ7UpzwH2SGd4RAujPAKDkM53sirwNFa7jH/Q3R2y1/QAcKQCgn9VH
pUSwTkR3c963BoCbNwG+W6Y=
=s7Vr
-----END PGP SIGNATURE-----

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Gaetano Mendola <mendola@bigfoot.com> writes:
> Gregory Stark wrote:
>> It's evidently guessing wrong about the limit being satisfied early. The
>> non-indexed restrictions might be pruning out a lot more records than the
>> planner expects. Or possibly the table is just full of dead records.

> Here the analyze result:

> explain analyze SELECT c.id, tsk, lir, nctr, nctn, ncts, rvel,ecp, pvcp, pvcc,pvcf,pvcl,ldcn,ogtd,sgti FROM t_OA_2_00_card c JOIN t_OA_2_00_dt dt ON (dt.card_id = c.id) WHERE ecp=18 AND _to >= 1500 AND _from <= 1550 ORDER BY nctr,nctn,ncts,rvel offset 0 limit 5;

> QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> Limit (cost=0.00..2125.12 rows=5 width=90) (actual time=3399923.424..3399960.174 rows=5 loops=1)
> -> Nested Loop (cost=0.00..4470402.02 rows=10518 width=90) (actual time=3399923.420..3399960.156 rows=5 loops=1)
> -> Index Scan using i_oa_2_00_card_keys on t_oa_2_00_card c (cost=0.00..3927779.56 rows=101872 width=90) (actual time=3399892.632..3399896.773 rows=50 loops=1)
> Filter: (ecp = 18)
> -> Index Scan using i_oa_2_00_dt_for on t_oa_2_00_dt dt (cost=0.00..5.31 rows=1 width=8) (actual time=1.264..1.264 rows=0 loops=50)
> Index Cond: (dt.card_id = c.id)
> Filter: ((_to >= 1500) AND (_from <= 1550))
> Total runtime: 3399960.277 ms

It's guessing that there are 101872 rows altogether that have ecp = 18.
Is that about right? If not, raising the statistics target for the
table might fix the problem. If it is about right, then you may be
stuck --- the problem then could be that the rows with ecp=18 aren't
uniformly scattered in the i_oa_2_00_card_keys ordering, but are
clustered near the end.

Greg's comment about dead rows might be correct too --- the actual
runtime for the indexscan seems kinda high even if it is scanning most
of the table. Also, if this query is important enough, clustering
by that index would improve matters, at the cost of possibly slowing
down other queries that use other indexes.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

> -> Index Scan using i_oa_2_00_dt_for on t_oa_2_00_dt dt (cost=0.00..5.31 rows=1 width=8) (actual time=1.264..1.264 rows=0 loops=50)
> Index Cond: (dt.card_id = c.id)
> Filter: ((_to >= 1500) AND (_from <= 1550))
> Total runtime: 3399960.277 ms

Also, are 1500 and 1550 user-supplied parameters or are they part of a small set of possible values? You could consider having a partial index on "card_id WHERE _to >= 1500 AND _from <= 1550". The numbers don't even have to match exactly as long as they include all the records the query needs.

Another possibility is using something like "cube" from contrib to build a GIST index on <_to,_from>. I think you would need to load gist_btree as well for the first column on card_id. It doesn't help every use case though, you would have to experiment.

But before experimenting with either of those things, what does "VACUUM VERBOSE t_oa_2_00_dt" say?

"Greg Stark" <greg.stark@enterprisedb.com> writes:
>> -> Index Scan using i_oa_2_00_dt_for on t_oa_2_00_dt dt (cost=0.00..5.31 rows=1 width=8) (actual time=1.264..1.264 rows=0 loops=50)
>> Index Cond: (dt.card_id = c.id)
>> Filter: ((_to >= 1500) AND (_from <= 1550))
>> Total runtime: 3399960.277 ms

> Also, are 1500 and 1550 user-supplied parameters or are they part of a small set of possible values? You could consider having a partial index on "card_id WHERE _to >= 1500 AND _from <= 1550". The numbers don't even have to match exactly as long as they include all the records the query needs.

That side of the join isn't where the problem is, though.

If you're willing to invent new indexes, one on ecp,nctr,nctn,ncts,rvel
would probably fix the performance issue very nicely.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Tom Lane wrote:
> "Greg Stark" <greg.stark@enterprisedb.com> writes:
>>> -> Index Scan using i_oa_2_00_dt_for on t_oa_2_00_dt dt (cost=0.00..5.31 rows=1 width=8) (actual time=1.264..1.264 rows=0 loops=50)
>>> Index Cond: (dt.card_id = c.id)
>>> Filter: ((_to >= 1500) AND (_from <= 1550))
>>> Total runtime: 3399960.277 ms
>
>> Also, are 1500 and 1550 user-supplied parameters or are they part of a small set of possible values? You could consider having a partial index on "card_id WHERE _to >= 1500 AND _from <= 1550". The numbers don't even have to match exactly as long as they include all the records the query needs.
>
> That side of the join isn't where the problem is, though.
>
> If you're willing to invent new indexes, one on ecp,nctr,nctn,ncts,rvel
> would probably fix the performance issue very nicely.
>

As always you are right, creating the index "ivan" btree (ecp, nctr, nctn, ncts, rvel)

that query with the limit responds now in the blink of an eye:


> explain analyze SELECT c.id, tsk, lir, nctr, nctn, ncts, rvel,ecp, pvcp, pvcc,pvcf,pvcl,ldcn,ogtd,sgti
FROM t_OA_2_00_card c JOIN t_OA_2_00_dt dt ON (dt.card_id = c.id)
WHERE ecp=18 AND _to >= 1500 AND _from <= 1550
ORDER BY nctr,nctn,ncts,rvel
offset 0 limit 5;
QUERY PLAN
- -----------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..370.03 rows=5 width=90) (actual time=0.102..0.608 rows=5 loops=1)
-> Nested Loop (cost=0.00..778392.80 rows=10518 width=90) (actual time=0.099..0.594 rows=5 loops=1)
-> Index Scan using ivan on t_oa_2_00_card c (cost=0.00..235770.34 rows=101872 width=90) (actual time=0.024..0.134 rows=50 loops=1)
Index Cond: (ecp = 18)
-> Index Scan using i_oa_2_00_dt_for on t_oa_2_00_dt dt (cost=0.00..5.31 rows=1 width=8) (actual time=0.006..0.006 rows=0 loops=50)
Index Cond: (dt.card_id = c.id)
Filter: ((_to >= 1500) AND (_from <= 1550))
Total runtime: 0.700 ms
(8 rows)


Regards
Gaetano Mendola
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFHo1SB7UpzwH2SGd4RAhTeAJ0WL49jjUgCWSrNopV/8L+rbOLaEgCfTDlh
crAHZYxxTYz6VqTDggqW7x0=
=dKey
-----END PGP SIGNATURE-----

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster