Re: speed w/ OFFSET/LIMIT

I found a kind of workaround by :

- first creating a view :
CREATE VIEW table_view AS
SELECT *
FROM table
ORDER BY main_field OFFSET x LIMIT 50

- then joining required tables
SELECT t.* , t1.flag1_details , ... , tn.flagn_details
FROM table_view t
JOINs....

A better solution anyway ?


On Tuesday 27 May 2003 15:45, Damien wrote:
> Hi !
>
> I'm running a pretty simple select query on a pretty large table (70000
> records). This table has some "flag" fields, each one textually explained
> by another table ( flag VARCHAR(2), flag_details VARCHAR(60))
>
> SELECT t.* , t1.flag1_details , ... , tn.flagn_details
> FROM table t
> NATURAL JOIN t1
> NATURAL JOIN ...
> NATURAL JOIN tn
> ORDER BY main_field OFFSET x LIMIT 50
>
> There is no where statement, this query is for display purposes on a web
> page. My problem is, where OFFSET is low, execution takes only a few
> milli-seconds, but where OFFSET is high (50 last results for example),
> execution can take 10 seconds...
> Is there anything I can do to speed up such a query ?
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html