Claudio Freire writes:
> Alternatively, a token startup cost could be added to those kinds of
> filtered sequential scans, when the filtering term is selective
> enough. That would offset the cost just a little bit, but enough to
> favor index over sequential on the right cases.
Maybe not so "tok
On Tue, Aug 6, 2013 at 3:46 AM, Ivan Voras wrote:
> Here are two more unexpected results. Same test table (1 mil. records,
> "id" is SERIAL PRIMARY KEY, PostgreSQL 9.1, VACUUM ANALYZE performed
> before the experiments):
>
> ivoras=# explain analyze select * from lt where id > 90 limit 10;
>
On Mon, Aug 5, 2013 at 6:54 PM, David Johnston wrote:
> Curious how much slower/faster these queries would run if you added:
>
> SELECT *, first_value(id) OVER (...), last_value(id) OVER (...)
> --note the window specifications need to overcome the "ORDER BY" limitation
> noted in the documentatio
On Tue, Aug 6, 2013 at 8:03 PM, Claudio Freire wrote:
> On Tue, Aug 6, 2013 at 7:56 PM, Mark Kirkwood
> wrote:
>> Hmm - I wonder if the lack or ORDER BY is part of the problem here. Consider
>> a similar query on pgbench_accounts:
>>
>> bench=# explain analyze select aid from pgbench_accounts whe
On Tue, Aug 6, 2013 at 7:56 PM, Mark Kirkwood
wrote:
> Hmm - I wonder if the lack or ORDER BY is part of the problem here. Consider
> a similar query on pgbench_accounts:
>
> bench=# explain analyze select aid from pgbench_accounts where aid > 10
> limit 20;
> QUERY PLAN
>
On 06/08/13 22:46, Ivan Voras wrote:
Here are two more unexpected results. Same test table (1 mil. records,
"id" is SERIAL PRIMARY KEY, PostgreSQL 9.1, VACUUM ANALYZE performed
before the experiments):
ivoras=# explain analyze select * from lt where id > 90 limit 10;
On Tue, Aug 6, 2013 at 3:04 AM, Ivan Voras wrote:
>
> ivoras=# explain analyze select * from lt order by id desc limit 10;
> QUERY
> PLAN
>
On Mon, Aug 5, 2013 at 9:22 PM, Josh Berkus wrote:
> Ivan,
>
> > Or, more generally, is there some set of circumstances under which the
> > catastrophic scenario will happen?
>
> Yes:
>
> SELECT * FROM table ORDER BY id DESC LIMIT 10 OFFSET 10
>
> This is the "high offset" problem, and affect
On Tue, Aug 6, 2013 at 7:46 AM, Ivan Voras wrote:
> ivoras=# explain analyze select * from lt where id > 90 limit 10;
>QUERY PLAN
> --
Here are two more unexpected results. Same test table (1 mil. records,
"id" is SERIAL PRIMARY KEY, PostgreSQL 9.1, VACUUM ANALYZE performed
before the experiments):
ivoras=# explain analyze select * from lt where id > 90 limit 10;
QUERY PLAN
-
On 6 August 2013 02:20, Michael Paquier wrote:
>
> On Tue, Aug 6, 2013 at 8:25 AM, Claudio Freire
> wrote:
>>
>> On Mon, Aug 5, 2013 at 8:04 PM, Ivan Voras wrote:
>> > SELECT * FROM table ORDER BY id DESC LIMIT 10 OFFSET 10
>> >
>> > SELECT * FROM table WHERE active ORDER BY id DESC LIMIT 10 OFF
Sergey Konoplev-2 wrote
> As an alternative solution for pagination (OFFSET) problem you might
> also use the "prev/next" technique, like
>
> SELECT * FROM table
> WHERE id > :current_last_id
> ORDER BY id LIMIT 10
>
> for "next", and
>
> SELECT * FROM (
> SELECT * FROM table
> WHERE id
On Mon, Aug 5, 2013 at 6:22 PM, Josh Berkus wrote:
>> Or, more generally, is there some set of circumstances under which the
>> catastrophic scenario will happen?
>
> Yes:
>
> SELECT * FROM table ORDER BY id DESC LIMIT 10 OFFSET 10
>
> This is the "high offset" problem, and affects all databas
Ivan,
> Or, more generally, is there some set of circumstances under which the
> catastrophic scenario will happen?
Yes:
SELECT * FROM table ORDER BY id DESC LIMIT 10 OFFSET 10
This is the "high offset" problem, and affects all databases which
support applications with paginated results, in
On Tue, Aug 6, 2013 at 8:25 AM, Claudio Freire wrote:
> On Mon, Aug 5, 2013 at 8:04 PM, Ivan Voras wrote:
> > SELECT * FROM table ORDER BY id DESC LIMIT 10 OFFSET 10
> >
> > SELECT * FROM table WHERE active ORDER BY id DESC LIMIT 10 OFFSET 10
>
> Did you try explain?
>
And did you run ANALYZE on
On Mon, Aug 5, 2013 at 8:04 PM, Ivan Voras wrote:
> SELECT * FROM table ORDER BY id DESC LIMIT 10 OFFSET 10
>
> SELECT * FROM table WHERE active ORDER BY id DESC LIMIT 10 OFFSET 10
Did you try explain?
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make change
Hello,
Assuming I have a huge table (doesn't fit in RAM), of which the most
important fields are "id" which is a SERIAL PRIMARY KEY and "active"
which is a boolean, and I'm issuing a query like:
SELECT * FROM table ORDER BY id DESC LIMIT 10
... is pgsql smart enough to use the index to fetch onl
17 matches
Mail list logo
