pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
qurey 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