On Fri, Jun 23, 2006 at 05:12:14PM +0300, Tzahi Fadida wrote:
> My initial reasoning was to avoid extra sorts but i guess that the planner
> just doesn't get the LIMIT 1. I see now that UNION should be better
> for the planner to undestand (not performance wise).
> However, UNION alone, doesn't seem to cut it.
> Following is an example. t7 has 2 attributes and a non-unique index on one
> attribute. here is a printout:
> explain analyze (select * from t7 where a4=113 LIMIT 1) UNION (select * from
> t7 where a2=139 LIMIT 1);
What are the indexes on? If you only have an index on a4, the latter
query has to be an index scan and there's no way to optimise it way.
> > Also, couldn't you just do:
> >
> > SELECT * FROM R
> > WHERE (a=3, b=6, ...)
> > OR (b=5, d=2, ...)
> > etc
>
> No, a filtering action is not enough since my goal is to only use indices
> when retrieving single tuples each time thus, if i will use OR i cannot
> control the number of tuples returned by each Or clause.
I must admit, this is a really strange way of doing it. For example, if
multiple rows match, the tuples eventually returned will be a random
selection of the rows that matched. Especially with the "limit 1"
there's no way the optimiser could combine the individual scans.
If you really need the "LIMIT 1" and you don't have full index coverage
then you're quite limited as to how it can be optimised.
> > > I am currently just writing the query as a string and open a cursor.
> > > Is there a simple way to use Datums instead of converting the attributes
> > > to strings to create a plan for SPI.
> > > 10x.
> >
> > I imagine SPI_prepare() and SPI_execp() would be used for this.
>
> I am already using SPI_prepare but it uses a query of the form of a char
> string, which i need to prepare and is quite long. I.e. if i have 100 tuples
> i wish to retrieve it can be very wasteful to prepare the string in memory
> and use SPI_prepare to prepare and later execute it.
> better to use directly the datums (which i already have deformed from
> previous operations).
I'm confused here too. I thought the datums you're talking about were
arguments, thus you could push them straight to SPI_execp(). But you
seem to be suggesting parts of the actual query are in datum form also?
Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.
