From: | Henrik Zagerholm <henke(at)mac(dot)se> |
---|---|
To: | Heikki Linnakangas <heikki(at)enterprisedb(dot)com> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Extreme slow select query 8.2.4 |
Date: | 2007-08-06 20:16:55 |
Message-ID: | 976CDC49-082B-4612-B6A4-4B46B5109829@mac.se |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
6 aug 2007 kl. 21:47 skrev Heikki Linnakangas:
> Henrik Zagerholm wrote:
>> I know the query retrieves way more which is really necessary to
>> show to
>> the user so I would gladly come up with a way to limit the query
>> so the
>> GUI doesn't hang for several minutes if a user does a bad search.
>> The problem is that I don't know a good way of limit the search
>> efficiently as only going on tbl_file with limit 100 could make the
>> query only to return 10 rows if the user doesn't have access to
>> 900 of
>> the files (This is what the join with tbl_acl does). Using cursors
>> doesn't help because I really don't retrieve that much data
>
> Could you just add a LIMIT 100 to the end of the query, if 100 rows is
> enough? That would cut the runtime of the query, if there's a quicker
> plan to retrieve just those 100 rows.
As you can see in the query I already have a limit 20 and it doesn't
make any difference as the query still does the big joins between
tbl_file, tbl_file_structure and tbl_acl.
This is why I think I have to come up with a way of using sub select
with internal limits. Maybe have a cursor like procedure using these
so I always get the correct number of lines back.
>
> Another alternative is to use statement_timeout. If a query takes
> longer
> than specified timeout, it's automatically aborted and an error is
> given.
Interesting! I'll take a look at that.
Thanks,
henrik
>
> --
> Heikki Linnakangas
> EnterpriseDB http://www.enterprisedb.com
From | Date | Subject | |
---|---|---|---|
Next Message | Henrik Zagerholm | 2007-08-06 21:29:45 | Re: [PERFORM] Planner making wrong decisions 8.2.4. Insane cost calculations. |
Previous Message | Heikki Linnakangas | 2007-08-06 19:47:22 | Re: Extreme slow select query 8.2.4 |