Skip site navigation (1) Skip section navigation (2)

Re: [INTERFACES] Re: [HACKERS] changes in 6.4

From: Bruce Momjian <maillist(at)candle(dot)pha(dot)pa(dot)us>
To: daveh(at)insightdist(dot)com (David Hartwig)
Cc: hannu(at)trust(dot)ee, pgsql-interfaces(at)postgreSQL(dot)org, hackers(at)postgreSQL(dot)org
Subject: Re: [INTERFACES] Re: [HACKERS] changes in 6.4
Date: 1998-07-15 23:39:05
Message-ID: 199807152339.TAA07145@candle.pha.pa.us (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-hackerspgsql-interfaces
> The results are profound.    Queries that used to scan tables because of the
> ORs, now make use of any indexes.   Thus, the size of the table has virtually
> no effect on performance.  Furthermore, queries that used to crash the
> backend, now run in under a second.
> 
> Currently the down sides are:
>     1. If there is no usable index, performance is significantly worse.  The
> patch does not check to make sure that there is a usable index.  I could use
> some pointers on this.
> 
>     2. Small tables are actually a bit slower than without the patch.
> 
>     3.  Not very elegant.    I am looking for a more generalized solution.
> I have lots of ideas, but I would need to know the backend much better before
> attempting any of them.   My favorite idea is before cnfify(), to factor the
> OR terms and pull out the constants into a virtual (temporary) table spaces.
> Then rewrite the query as a join.   The optimizer will (should) treat the new
> query accordingly.  This assumes that an efficient factoring algorithm exists
> and that temporary tables can exist in the heap.

OK, I have an idea.  Just today, we allow:

	select *
	from tab1
	where val in (
		select x from tab2
		union
		select y from tab3
	)

How about if instead of doing:

	select * from tab1 where val = 3
	union
	select * from tab1 where val = 4
	...

you change it to:
	
	select * from tab1 where val in (
		select 3
		union
		select 4
	)

This may be a big win.  You aren't running the same query over and over
again, with the same joins, and just a different constant.

Let me know.

If it fails for some reason, it is possible my subselect union code has
a bug, so let me know.


-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist(at)candle(dot)pha(dot)pa(dot)us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

In response to

Responses

pgsql-hackers by date

Next:From: Vadim MikheevDate: 1998-07-15 23:46:25
Subject: proposals for LLL, part 1
Previous:From: David HartwigDate: 1998-07-15 22:16:02
Subject: Re: [INTERFACES] Re: [HACKERS] changes in 6.4

pgsql-interfaces by date

Next:From: Bruce MomjianDate: 1998-07-16 01:22:33
Subject: Re: [INTERFACES] Re: [HACKERS] changes in 6.4
Previous:From: Ross, BrentonDate: 1998-07-15 23:37:35
Subject: Distributed Applications

pgsql-general by date

Next:From: 1214Date: 1998-07-15 23:41:41
Subject: Mega-Cash Wants To Advertise On Your Site!
Previous:From: David HartwigDate: 1998-07-15 22:16:02
Subject: Re: [INTERFACES] Re: [HACKERS] changes in 6.4

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group