pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedqurey plan and indices
7+ messages / 5 participants
[nested] [flat]
* qurey plan and indices
@ 1999-08-05 12:08 MESZAROS Attila <tilla@chiara.csoma.elte.hu>
0 siblings, 1 reply; 7+ messages in thread
From: MESZAROS Attila @ 1999-08-05 12:08 UTC (permalink / raw)
To: pgsql-sql
Hi,
I've experienced a brutal speedup (order of 2) separateing the following
subquery and making it manually:
explain select name,description
from descriptions
where in (select name
from descriptions
where description like '%Bankverbindung%');
Seq Scan on descriptions (cost=163.98 rows=3575 width=24)
SubPlan
-> Seq Scan on descriptions (cost=163.98 rows=2 width=12)
[I had no patient to wait the resuls...]
If I give manually the subquery it gives an immediate response (5 records
at all). If I copy manually that 5 record as constants after 'in', then I
receive an immediate response from the main query too!
explain select name,description from descriptions where name in
('accounts', 'no_account', 'no_account', 'no_acc', 'account');
Index Scan using desc_ind3, desc_ind3, desc_ind3, desc_ind3 on
descriptions (cost=9.01 rows=36 width=24)
I saw in the archive that in, and not in can cause performance
degradation, but this is not the case now. booth queries are using 'in'
Why can't be the subquery evaluated first, and then run the main query
with the results?
============== the stucture: ===============================
CREATE TABLE "descriptions" (
"objectid" int4 DEFAULT nextval ( 'DescSeq' ) NOT NULL,
"ts" int4 NOT NULL,
"classid" int4 NOT NULL,
"languageid" int4 NOT NULL,
"priority" int4 NOT NULL,
"name" character varying(50) NOT NULL,
"description" character varying(200));
CREATE INDEX "desc_ind3" on "descriptions" using btree ( "name"
"varchar_ops" );
CREATE INDEX "desc_ind2" on "descriptions" using btree ( "description"
"varchar_ops" );
CREATE INDEX "desc_ind1" on "descriptions" using btree ( "classid"
"int4_ops", "name" "varchar_ops" );
CREATE UNIQUE INDEX "desc_idx" on "descriptions" using btree ( "classid"
"int4_ops", "languageid" "int4_ops", "name" "varchar_ops" );
CREATE UNIQUE INDEX "descriptions_pkey" on "descriptions" using btree (
"objectid" "int4_ops" );
version: postgresql 6.5, debian potato.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [SQL] qurey plan and indices
@ 1999-08-05 14:18 Patrick Giagnocavo <a222@redrose.net>
parent: MESZAROS Attila <tilla@chiara.csoma.elte.hu>
0 siblings, 2 replies; 7+ messages in thread
From: Patrick Giagnocavo @ 1999-08-05 14:18 UTC (permalink / raw)
To: ; +Cc: pgsql-sql
MESZAROS Attila wrote:
>
> Hi,
>
> I've experienced a brutal speedup (order of 2) separateing the following
> subquery and making it manually:
>
> explain select name,description
> from descriptions
> where in (select name
> from descriptions
> where description like '%Bankverbindung%');
>
> Seq Scan on descriptions (cost=163.98 rows=3575 width=24)
> SubPlan
> -> Seq Scan on descriptions (cost=163.98 rows=2 width=12)
> [I had no patient to wait the resuls...]
>
Correct me if I am wrong, however if using
LIKE '%something%'
(which means, the field contains 'something' somewhere in the field)
there is never a chance to use the index you have created - a
sequential table scan MUST be made, thus you have to read all 3575
rows to return the set.
However, if you change your query so that you are looking for
LIKE 'Bankverbindung%'
then an index can be used becase you know what the first few
characters are known.
Hope this helps,
Cordially
Patrick Giagnocavo
a222@redrose.net
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [SQL] qurey plan and indices
@ 1999-08-05 14:39 Tom Lane <tgl@sss.pgh.pa.us>
parent: Patrick Giagnocavo <a222@redrose.net>
1 sibling, 2 replies; 7+ messages in thread
From: Tom Lane @ 1999-08-05 14:39 UTC (permalink / raw)
To: Patrick Giagnocavo <a222@redrose.net>; +Cc: pgsql-sql
Patrick Giagnocavo <a222@redrose.net> writes:
> MESZAROS Attila wrote:
>>
>> I've experienced a brutal speedup (order of 2) separateing the following
>> subquery and making it manually:
>>
>> explain select name,description
>> from descriptions
>> where in (select name
>> from descriptions
>> where description like '%Bankverbindung%');
> Correct me if I am wrong, however if using
> LIKE '%something%'
> (which means, the field contains 'something' somewhere in the field)
> there is never a chance to use the index you have created - a
> sequential table scan MUST be made, thus you have to read all 3575
> rows to return the set.
Yes. The inner query must happen by sequential scan. But the outer
query can use an index if it is in the form
... where (name = 'a') or (name = 'b') or (name = 'c');
which is what "where name in ('a','b','c')" gets translated to.
However, the *real* problem here is that the inner query is treated
as a subplan, which means it is re-evaluated for each tuple scanned
by the outer query. This is not very bright; the system ought to
notice that the inner query does not depend on the state of the
outer query, and do it only once. There is already a notion of
InitPlan vs. SubPlan. I'm not sure why this case is being classified
as a SubPlan, but it sure looks like it ought to be done as an
InitPlan...
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [SQL] qurey plan and indices
@ 1999-08-05 14:51 MESZAROS Attila <tilla@chiara.csoma.elte.hu>
parent: Patrick Giagnocavo <a222@redrose.net>
1 sibling, 0 replies; 7+ messages in thread
From: MESZAROS Attila @ 1999-08-05 14:51 UTC (permalink / raw)
To: Patrick Giagnocavo <a222@redrose.net>; +Cc: pgsql-sql
Hi,
> LIKE '%something%'
> (which means, the field contains 'something' somewhere in the field)
I should have to remove that % sign...
this is not the real problem, bceause the subquery alone with
booth the % signs responds faster than a second.
I think the real problem is that, the subquery is evaluated 3575
times :((((
If you see the plan of the direct manul query in my previous mail
you will see that there is no sequential search at all....
what makes the difference????
Attila
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [SQL] qurey plan and indices
@ 1999-08-05 14:59 MESZAROS Attila <tilla@chiara.csoma.elte.hu>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 7+ messages in thread
From: MESZAROS Attila @ 1999-08-05 14:59 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Patrick Giagnocavo <a222@redrose.net>; pgsql-sql
> However, the *real* problem here is that the inner query is treated
> as a subplan, which means it is re-evaluated for each tuple scanned
> by the outer query. This is not very bright; the system ought to
> notice that the inner query does not depend on the state of the
> outer query, and do it only once. There is already a notion of
> InitPlan vs. SubPlan. I'm not sure why this case is being classified
> as a SubPlan, but it sure looks like it ought to be done as an
> InitPlan...
So this means, it is a bug somewhere in the planner?
who should we report this??
Attila
any workarounds?
[better than temptables?]
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [SQL] qurey plan and indices
@ 1999-08-06 02:09 Vadim Mikheev <vadim@krs.ru>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 7+ messages in thread
From: Vadim Mikheev @ 1999-08-06 02:09 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Patrick Giagnocavo <a222@redrose.net>; pgsql-sql
Tom Lane wrote:
>
> Yes. The inner query must happen by sequential scan. But the outer
> query can use an index if it is in the form
> ... where (name = 'a') or (name = 'b') or (name = 'c');
> which is what "where name in ('a','b','c')" gets translated to.
>
> However, the *real* problem here is that the inner query is treated
> as a subplan, which means it is re-evaluated for each tuple scanned
> by the outer query. This is not very bright; the system ought to
> notice that the inner query does not depend on the state of the
> outer query, and do it only once. There is already a notion of
> InitPlan vs. SubPlan. I'm not sure why this case is being classified
> as a SubPlan, but it sure looks like it ought to be done as an
> InitPlan...
Not in all cases. First, to use InitPlan you would have to add
DISTINCT to subquery to avoid duplicates. Second, there is another
way of optimization - cache/hash subquery results to avoid
re-execution of subquery plan. Planner should make decision what
way to follow.
Vadim
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [SQL] qurey plan and indices
@ 1999-08-06 02:33 Bruce Momjian <maillist@candle.pha.pa.us>
parent: Vadim Mikheev <vadim@krs.ru>
0 siblings, 0 replies; 7+ messages in thread
From: Bruce Momjian @ 1999-08-06 02:33 UTC (permalink / raw)
To: Vadim Mikheev <vadim@krs.ru>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Patrick Giagnocavo <a222@redrose.net>; pgsql-sql
> Not in all cases. First, to use InitPlan you would have to add
> DISTINCT to subquery to avoid duplicates. Second, there is another
> way of optimization - cache/hash subquery results to avoid
> re-execution of subquery plan. Planner should make decision what
> way to follow.
Yep, that's what I was looking for.
--
Bruce Momjian | http://www.op.net/~candle
maillist@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~1999-08-06 02:33 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-08-05 12:08 qurey plan and indices MESZAROS Attila <tilla@chiara.csoma.elte.hu>
1999-08-05 14:18 ` Patrick Giagnocavo <a222@redrose.net>
1999-08-05 14:39 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-08-05 14:59 ` MESZAROS Attila <tilla@chiara.csoma.elte.hu>
1999-08-06 02:09 ` Vadim Mikheev <vadim@krs.ru>
1999-08-06 02:33 ` Bruce Momjian <maillist@candle.pha.pa.us>
1999-08-05 14:51 ` MESZAROS Attila <tilla@chiara.csoma.elte.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