I wrote:
This didn't matter a whole lot back when the planner couldn't reorder
outer joins, but now that it can, it's more important that the estimates
be accurate.
I'm not sure if this is feasible to fix before 8.2, but I'll take a
look.
Actually, the changes were far more localized than I
I wrote:
> This didn't matter a whole lot back when the planner couldn't reorder
> outer joins, but now that it can, it's more important that the estimates
> be accurate.
> I'm not sure if this is feasible to fix before 8.2, but I'll take a
> look.
Actually, the changes were far more localized th
"Thomas H." <[EMAIL PROTECTED]> writes:
>> Um, what's the datatype of sm_info_ean and dvd_ean exactly?
> varchar(15) and varchar(14)
OK. I was wondering if you'd tried to use the new contrib/isn code and
it was messing up the estimates somehow. Seems like a red herring.
After looking more clos
SELECT * FROM shop.dvds
LEFT JOIN oldtables.movies ON mov_id = dvd_mov_id
LEFT JOIN shop.data_soundmedia ON sm_info_ean = dvd_ean
WHERE (lower(mov_name) LIKE '%superman re%' OR lower(dvd_name) like
'%superman re%' OR lower(dvd_edition) LIKE '%superman re%')
Um, what's the datatype of sm_info_ean
"Thomas H." <[EMAIL PROTECTED]> writes:
> SELECT * FROM shop.dvds
> LEFT JOIN oldtables.movies ON mov_id = dvd_mov_id
> LEFT JOIN shop.data_soundmedia ON sm_info_ean = dvd_ean
> WHERE (lower(mov_name) LIKE '%superman re%' OR lower(dvd_name) like
> '%superman re%' OR lower(dvd_edition) LIKE '%super
Thomas H. wrote:
OK - in that case try explicit subqueries:
SELECT ... FROM
(SELECT * FROM shop.dvds
LEFT JOIN shop.oldtables.movies
WHERE lower(mov_name) LIKE ...
) AS bar
LEFT JOIN shop.data_soundmedia
same result, have tried this as well (22sec). it's the LEFT JOIN
shop.data_soundmedia
OK - in that case try explicit subqueries:
SELECT ... FROM
(SELECT * FROM shop.dvds
LEFT JOIN shop.oldtables.movies
WHERE lower(mov_name) LIKE ...
) AS bar
LEFT JOIN shop.data_soundmedia
same result, have tried this as well (22sec). it's the LEFT JOIN
shop.data_soundmedia for which the plan
Thomas H. wrote:
Try putting your conditions as part of the join:
SELECT * FROM shop.dvds
LEFT JOIN
oldtables.movies
ON
mov_id = dvd_mov_id
AND (
lower(mov_name) LIKE '%superman re%'
OR lower(dvd_name) like '%superman re%'
OR lower(dvd_edition) LIKE '%superman re%'
)
LEFT JOIN
Try putting your conditions as part of the join:
SELECT * FROM shop.dvds
LEFT JOIN
oldtables.movies
ON
mov_id = dvd_mov_id
AND (
lower(mov_name) LIKE '%superman re%'
OR lower(dvd_name) like '%superman re%'
OR lower(dvd_edition) LIKE '%superman re%'
)
LEFT JOIN shop.data_soundme
1. You MUST sequence scan dvds, as there is no way to do an index search
on a like with % at the beginning.
2. You are asking for a left join on dvds, which means you want all
records, so you must sequence scan dvds. The filters are all OR, so you
can't say that a records is excluded until AFTE
Thomas H. wrote:
hi list.
as soon as i left-join an additional table, the query takes 24sec
instead of 0.2sec, although the added fields have no impact on the
resultset:
SELECT * FROM shop.dvds
LEFT JOIN oldtables.movies ON mov_id = dvd_mov_id
WHERE (lower(mov_name) LIKE
TECTED] on behalf of Richard Huxton
Sent: Thu 11/9/2006 1:22 AM
To: Thomas H.
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] planer picks a bad plan (seq-scan instead of
index)
Thomas H. wrote:
>
> SELECT * FROM shop.dvds
> LEFT JOIN oldtabl
Thomas H. wrote:
SELECT * FROM shop.dvds
LEFT JOIN oldtables.movies ON mov_id = dvd_mov_id
LEFT JOIN shop.data_soundmedia ON sm_info_ean = dvd_ean
WHERE (lower(mov_name) LIKE '%superman re%' OR lower(dvd_name) like
'%superman re%' OR lower(dvd_edition) LIKE '%superman re%')
Thomas H. wrote:
hi list.
as soon as i left-join an additional table, the query takes 24sec
instead of 0.2sec, although the added fields have no impact on the
resultset:
SELECT * FROM shop.dvds
LEFT JOIN oldtables.movies ON mov_id = dvd_mov_id
WHERE (lower(mov_name) LIKE
Have you run analyze on all the three tables since creating the
database?
yes. even a forced ANALYZE FULL after the table loads: the tables were
TRUNCATE'd, refilled and ANALYZE FULL'ed some minutes before the tests.
there where no UPDATEs after the INSERTs...
- thomas
-
Have you run analyze on all the three tables since creating the
database?
What On Thu, 2006-11-09 at 02:31 +0100, Thomas H. wrote:
> hi list.
>
> as soon as i left-join an additional table, the query takes 24sec instead of
> 0.2sec, although the added fields have no impact on the resultset:
>
hi list.
as soon as i left-join an additional table, the query takes 24sec instead of
0.2sec, although the added fields have no impact on the resultset:
SELECT * FROM shop.dvds
LEFT JOIN oldtables.movies ON mov_id = dvd_mov_id
WHERE (lower(mov_name) LIKE '%superman re%' OR
17 matches
Mail list logo
