We have a large (several million row) table with a field containing
URLs. Now, funny thing about URLs: they mostly start with a common
substring ("http://www.";). But not all the rows start with this, so we
can't just lop off the first N characters. However, we noticed some time
ago that an ind
Jeff Boes <[EMAIL PROTECTED]> writes:
> Is this a bug in the optimizer? Or did something
> change about the way functional indexes are used?
In 7.3, the only possible plan for these queries was a nestloop or
nestloop with inner indexscan, because the planner could not generate
merge or hash joins
> I can't get the following statement to complete with reasonable time.
Upgraded to 7.4.1, and realized that NOT IN is far more efficient than
IN, EXISTS or NOT EXISTS, at least for the amount and distribution of
data that I have. Here are some numbers from before and after performing
the problema
Folks,
Have an interesting issue with a complex query, where apparently I need to
twist the query planner's arm, and am looking for advice on how to do so.
The situation: I have a table, events, with about 300,000 records.
It does an outer join to a second table, cases, with about 150,000 rec
Josh,
I'm sure the big brains have a better suggestion, but in the mean time
could you do something as simple as:
SELECT *
FROM (select * from events where event_date BETWEEN 'date1' AND 'date2') e
LEFT OUTER JOIN cases ON e.case_id = cases.case_id;
Thanks,
Peter Darley
-Original Mes
Folks,
Hmmm posted too soon. Figured out the problem:
The planner can't, or doesn't want to, use an index on (event_date,
(event_date + duration)) where the first column is an ascending sort and the
second a descending sort.So I've coded a workaround that's quite
inelegant but does get th
Peter,
> I'm sure the big brains have a better suggestion, but in the mean time
> could you do something as simple as:
>
> SELECT *
> FROM (select * from events where event_date BETWEEN 'date1' AND 'date2') e
> LEFT OUTER JOIN cases ON e.case_id = cases.case_id;
Thanks, but that doens't wo
On Wed, 18 Feb 2004, Josh Berkus wrote:
> The planner can't, or doesn't want to, use an index on (event_date,
> (event_date + duration)) where the first column is an ascending sort and the
> second a descending sort.So I've coded a workaround that's quite
> inelegant but does get the correct r
Stephan,
> Can you give more information? I know that I'm not exactly certain what
> the situation is from the above and the original query/explain piece.
>
Believe me, if I posted the query it wouldn't help.Heck, I'd have trouble
following it without my notes.
a simplifed version:
SELEC
On Wed, 18 Feb 2004, Josh Berkus wrote:
> Stephan,
>
> > Can you give more information? I know that I'm not exactly certain what
> > the situation is from the above and the original query/explain piece.
> >
>
> Believe me, if I posted the query it wouldn't help.Heck, I'd have trouble
> follow
Josh Berkus <[EMAIL PROTECTED]> writes:
> SELECT events.*, cases.case_name
> FROM events LEFT OUTER JOIN cases ON events.case_id = cases.case_id
> WHERE (event_date >= '2004-03-05' OR (event_date + duration) <= '2004-02-18')
> AND events.status <> 0;
> ... this is to get me all vaild events
Tom,
First off, you are correct, I swapped the dates when typing the simplified
query into e-mail.
> create index evi on events (event_date, ('ref-date'-event_date-duration))
>
> event_date <= 'end-date'
> AND ('ref-date'-event_date-duration) <= 'ref-date'-'start-date'
>
> where 'ref-date' is
Josh Berkus <[EMAIL PROTECTED]> writes:
> Knowing that events are never more than one month long for this
> application, I can do:
> "WHERE event.event_date >= (begin_date - '1 month) AND event.event_date <=
> end_date"
> ... which works because I have a child table which has event information b
13 matches
Mail list logo
