I looked into the distribution of the filenames, in particular I ran a
query to see how for into the table the 1st filename would be found.
photoshelter=# select count(*) from ps_image where lower(file_name) <
'a-400-001.jpg';
count
-
8915832
As you can see the first row is al
The 'in' form and 'join' form produce identical plans for both limit
and non-limit versions of the query, which I actually think reflects
well on the query planner. I also tried a form of the query with the
subselect in the from clause to try and force the order the tables
were evaluated
I had tried using exists but both the forms of the query (with limit
and without) performed much worse.
James
On May 1, 2009, at 4:22 AM, Adam Ruth wrote:
You could try changing the IN to an EXISTS, that may alter how the
optimizer weighs the limit.
SELECT ID FROM ps_image WHERE EXI
James Nelson writes:
> Hi, I'm hoping you guys can help with improving this query I'm having
> a problem with. The main problem is that the query plan changes
> depending on the value of the LIMIT clause, with small values using a
> poor plan and running very slowly. The two times are roughl
EXISTS won't help much either, postgresql is not too fast, when it
comes to that sort of approach.
join is always going to be fast, it is about time you learn joins and
use them ;)
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
use join instead of where in();
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
You could try changing the IN to an EXISTS, that may alter how the
optimizer weighs the limit.
SELECT ID FROM ps_image WHERE EXISTS (SELECT null FROM
ps_gallery_image WHERE gallery_id ='G7ejKGoWS_cY' and image_id =
ps_image.id) ORDER BY LOWER(FILE_NAME) ASC
On 30/04/2009, at 3:51 AM,
Hi, I'm hoping you guys can help with improving this query I'm having
a problem with. The main problem is that the query plan changes
depending on the value of the LIMIT clause, with small values using a
poor plan and running very slowly. The two times are roughly 5 minutes
for the bad pl
