pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedExecution time problem
3+ messages / 2 participants
[nested] [flat]
* Execution time problem
@ 2001-12-18 06:03 Iklódi Lajos <il@mithrandir.hu>
0 siblings, 1 reply; 3+ messages in thread
From: Iklódi Lajos @ 2001-12-18 06:03 UTC (permalink / raw)
To: pgsql-sql
Can anybody help me to resolve a problem?
In the first example, when Postgres uses index, execution time is good.
In the second example, tables are with the same structure, but Postgres
uses seq scan instead of using unique index "rentet_rttsz_key", so
execution time becomes very bad.
EXAMPLE 1
test1=# explain
test1-# select rtcikkod, vttsz
test1-# from rvtet, rentet
test1-# where
test1-# rvtet.vttsz= 13524
test1-# and rvtet.vtrtsz=rentet.rttsz
test1-# ;
NOTICE: QUERY PLAN:
Nested Loop (cost=0.00..4.16 rows=1 width=16)
-> Index Scan using rvtet_tszrtsz on rvtet (cost=0.00..2.05 rows=1
width=8)
-> Index Scan using rentet_rttsz_key on rentet (cost=0.00..2.09
rows=1 width=8)
EXPLAIN
test1=# select count(*) from rvtet;
count
-------
13112
(1 row)
test1=# select count(*) from rentet;
count
-------
13571
(1 row)
EXAMPLE 2
test2=# explain
test2-# select rtcikkod, vttsz
test2-# from rvtet, rentet
test2-# where
test2-# rvtet.vttsz= 13524
test2-# and rvtet.vtrtsz=rentet.rttsz
test2-# ;
NOTICE: QUERY PLAN:
Merge Join (cost=1726.50..1927.18 rows=24219 width=16)
-> Sort (cost=131.77..131.77 rows=152 width=8)
-> Index Scan using rvtet_tszrtsz on rvtet (cost=0.00..126.25
rows=152 width=8)
-> Sort (cost=1594.73..1594.73 rows=15902 width=8)
-> Seq Scan on rentet (cost=0.00..485.02 rows=15902 width=8)
EXPLAIN
test2=# \di rentet
List of relations
Name | Type | Owner
------------------+-------+-------
rentet_rtsz | index | www
rentet_rttsz_key | index | www
(2 rows)
test2=# select count(*) from rentet;
count
-------
15902
(1 row)
test2=# select count(*) from rvtet;
count
-------
15230
(1 row)
Thanks for everyone.
Lajos Iklodi
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Execution time problem
@ 2001-12-18 06:57 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Iklódi Lajos <il@mithrandir.hu>
0 siblings, 1 reply; 3+ messages in thread
From: Stephan Szabo @ 2001-12-18 06:57 UTC (permalink / raw)
To: Iklódi Lajos <il@mithrandir.hu>; +Cc: pgsql-sql
On Tue, 18 Dec 2001, [iso-8859-2] Iklódi Lajos wrote:
> Can anybody help me to resolve a problem?
>
> In the first example, when Postgres uses index, execution time is good.
> In the second example, tables are with the same structure, but Postgres
> uses seq scan instead of using unique index "rentet_rttsz_key", so
> execution time becomes very bad.
Have you run vacuum analyze? What version(s) are you running.
Given that the second seems to be estimating over 24000 matching
rows and the first 1, that probably has something to do with it.
>
> EXAMPLE 1
>
> test1=# explain
> test1-# select rtcikkod, vttsz
> test1-# from rvtet, rentet
> test1-# where
> test1-# rvtet.vttsz= 13524
> test1-# and rvtet.vtrtsz=rentet.rttsz
> test1-# ;
> NOTICE: QUERY PLAN:
>
> Nested Loop (cost=0.00..4.16 rows=1 width=16)
> -> Index Scan using rvtet_tszrtsz on rvtet (cost=0.00..2.05 rows=1
> width=8)
> -> Index Scan using rentet_rttsz_key on rentet (cost=0.00..2.09
> rows=1 width=8)
>
> EXPLAIN
> test1=# select count(*) from rvtet;
> count
> -------
> 13112
> (1 row)
>
> test1=# select count(*) from rentet;
> count
> -------
> 13571
> (1 row)
>
>
> EXAMPLE 2
>
> test2=# explain
> test2-# select rtcikkod, vttsz
> test2-# from rvtet, rentet
> test2-# where
> test2-# rvtet.vttsz= 13524
> test2-# and rvtet.vtrtsz=rentet.rttsz
> test2-# ;
> NOTICE: QUERY PLAN:
>
> Merge Join (cost=1726.50..1927.18 rows=24219 width=16)
> -> Sort (cost=131.77..131.77 rows=152 width=8)
> -> Index Scan using rvtet_tszrtsz on rvtet (cost=0.00..126.25
> rows=152 width=8)
> -> Sort (cost=1594.73..1594.73 rows=15902 width=8)
> -> Seq Scan on rentet (cost=0.00..485.02 rows=15902 width=8)
>
> EXPLAIN
> test2=# \di rentet
> List of relations
> Name | Type | Owner
> ------------------+-------+-------
> rentet_rtsz | index | www
> rentet_rttsz_key | index | www
> (2 rows)
>
> test2=# select count(*) from rentet;
> count
> -------
> 15902
> (1 row)
>
> test2=# select count(*) from rvtet;
> count
> -------
> 15230
> (1 row)
>
>
>
> Thanks for everyone.
>
> Lajos Iklodi
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: 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
>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Execution time problem
@ 2001-12-18 20:36 Iklódi Lajos <il@mithrandir.hu>
parent: Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 0 replies; 3+ messages in thread
From: Iklódi Lajos @ 2001-12-18 20:36 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: pgsql-sql
Thanks for the good idea! It really decided my problems. It was on 7.1.3.
Lajos
Stephan Szabo írta:
> On Tue, 18 Dec 2001, [iso-8859-2] Iklódi Lajos wrote:
>
> > Can anybody help me to resolve a problem?
> >
> > In the first example, when Postgres uses index, execution time is good.
> > In the second example, tables are with the same structure, but Postgres
> > uses seq scan instead of using unique index "rentet_rttsz_key", so
> > execution time becomes very bad.
>
> Have you run vacuum analyze? What version(s) are you running.
> Given that the second seems to be estimating over 24000 matching
> rows and the first 1, that probably has something to do with it.
>
> >
> > EXAMPLE 1
> >
> > test1=# explain
> > test1-# select rtcikkod, vttsz
> > test1-# from rvtet, rentet
> > test1-# where
> > test1-# rvtet.vttsz= 13524
> > test1-# and rvtet.vtrtsz=rentet.rttsz
> > test1-# ;
> > NOTICE: QUERY PLAN:
> >
> > Nested Loop (cost=0.00..4.16 rows=1 width=16)
> > -> Index Scan using rvtet_tszrtsz on rvtet (cost=0.00..2.05 rows=1
> > width=8)
> > -> Index Scan using rentet_rttsz_key on rentet (cost=0.00..2.09
> > rows=1 width=8)
> >
> > EXPLAIN
> > test1=# select count(*) from rvtet;
> > count
> > -------
> > 13112
> > (1 row)
> >
> > test1=# select count(*) from rentet;
> > count
> > -------
> > 13571
> > (1 row)
> >
> >
> > EXAMPLE 2
> >
> > test2=# explain
> > test2-# select rtcikkod, vttsz
> > test2-# from rvtet, rentet
> > test2-# where
> > test2-# rvtet.vttsz= 13524
> > test2-# and rvtet.vtrtsz=rentet.rttsz
> > test2-# ;
> > NOTICE: QUERY PLAN:
> >
> > Merge Join (cost=1726.50..1927.18 rows=24219 width=16)
> > -> Sort (cost=131.77..131.77 rows=152 width=8)
> > -> Index Scan using rvtet_tszrtsz on rvtet (cost=0.00..126.25
> > rows=152 width=8)
> > -> Sort (cost=1594.73..1594.73 rows=15902 width=8)
> > -> Seq Scan on rentet (cost=0.00..485.02 rows=15902 width=8)
> >
> > EXPLAIN
> > test2=# \di rentet
> > List of relations
> > Name | Type | Owner
> > ------------------+-------+-------
> > rentet_rtsz | index | www
> > rentet_rttsz_key | index | www
> > (2 rows)
> >
> > test2=# select count(*) from rentet;
> > count
> > -------
> > 15902
> > (1 row)
> >
> > test2=# select count(*) from rvtet;
> > count
> > -------
> > 15230
> > (1 row)
> >
> >
> >
> > Thanks for everyone.
> >
> > Lajos Iklodi
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: 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
> >
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2001-12-18 20:36 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-12-18 06:03 Execution time problem Iklódi Lajos <il@mithrandir.hu>
2001-12-18 06:57 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-18 20:36 ` Iklódi Lajos <il@mithrandir.hu>
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