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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-hackers pgsql-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)
From | Date | Subject | |
---|---|---|---|
Next Message | 1214 | 1998-07-15 23:41:41 | Mega-Cash Wants To Advertise On Your Site! |
Previous Message | David Hartwig | 1998-07-15 22:16:02 | Re: [INTERFACES] Re: [HACKERS] changes in 6.4 |
From | Date | Subject | |
---|---|---|---|
Next Message | Vadim Mikheev | 1998-07-15 23:46:25 | proposals for LLL, part 1 |
Previous Message | David Hartwig | 1998-07-15 22:16:02 | Re: [INTERFACES] Re: [HACKERS] changes in 6.4 |
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 1998-07-16 01:22:33 | Re: [INTERFACES] Re: [HACKERS] changes in 6.4 |
Previous Message | Ross, Brenton | 1998-07-15 23:37:35 | Distributed Applications |