I hate to be "that guy" but, Is this is still an issue 5 years later?? I
can't seem to get Gin/btree to use my ORDER BY column with a LIMIT no matter
what I try.
My best idea was to cluster the database by the ORDER BY column and then
just hope the index returns them in the order in the table...
On Wed, Jul 29, 2009 at 11:29 AM, Tom Lane wrote:
> Ain't transactional DDL wonderful?
Yes. :-)
...Robert
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Robert Haas writes:
> Hmm, good point. It seems like it would be useful to force the
> planner into use the other plan and get EXPLAIN ANALYZE output for
> that for comparison purposes, but off the top of my head I don't know
> how to do that.
The standard way is
begin;
drop ind
On Wed, Jul 29, 2009 at 10:22 AM, Tom Lane wrote:
> Robert Haas writes:
>> If love is an uncommon word, there's no help for queries of this type
>> being slow unless the GIN index can return the results in order. But
>> if love is a common word, then it would be faster to do an index scan
>> by t
If love is an uncommon word, there's no help for queries of this type
being slow unless the GIN index can return the results in order. But
if love is a common word, then it would be faster to do an index scan
by timestamp on the baserel and then treat comment_tsv @@
plainto_tsquery('love') as a
Robert Haas writes:
> If love is an uncommon word, there's no help for queries of this type
> being slow unless the GIN index can return the results in order. But
> if love is a common word, then it would be faster to do an index scan
> by timestamp on the baserel and then treat comment_tsv @@
>
On Mon, Jul 20, 2009 at 8:12 AM, Oleg Bartunov wrote:
>> Here's a couple of queries:
>>
>> archive=> explain analyze select * from a where comment_tsv @@
>> plainto_tsquery('love') order by timestamp desc limit 24 offset 0;
>>
>> QUERY PLAN
>> --
>> Limit (cost=453248.73..453248.79 rows=2
On Tue, 21 Jul 2009, Krade wrote:
On 7/21/2009 11:32, valgog wrote:
Hi,
There is a problem with GIN and GIST indexes, that they cannot be used
by the ORDER BY. Maybe it will be a nice idea to ask Oleg to make it
possible to use the b-tree columns in GIST or GIN to make the sort
easier, but I h
On 7/21/2009 11:32, valgog wrote:
Hi,
There is a problem with GIN and GIST indexes, that they cannot be used
by the ORDER BY. Maybe it will be a nice idea to ask Oleg to make it
possible to use the b-tree columns in GIST or GIN to make the sort
easier, but I have no idea how difficult it will be
On Tue, 21 Jul 2009, valgog wrote:
There is a problem with GIN and GIST indexes, that they cannot be used
by the ORDER BY. Maybe it will be a nice idea to ask Oleg to make it
possible to use the b-tree columns in GIST or GIN to make the sort
easier, but I have no idea how difficult it will be to
On Jul 21, 6:06 am, scott.marl...@gmail.com (Scott Marlowe) wrote:
> On Mon, Jul 20, 2009 at 9:35 PM, Krade wrote:
> > But I think I might just do:
> > select * from a where comment_tsv @@ plainto_tsquery('query') and timestamp
> >> cast(floor(extract(epoch from CURRENT_TIMESTAMP) - 864000) as inte
On Mon, Jul 20, 2009 at 9:35 PM, Krade wrote:
> But I think I might just do:
> select * from a where comment_tsv @@ plainto_tsquery('query') and timestamp
>> cast(floor(extract(epoch from CURRENT_TIMESTAMP) - 864000) as integer)
> order by timestamp desc limit 24 offset 0;
>
> And if I get less th
On 7/21/2009 2:13, Devin Ben-Hur wrote:
Have you tried make the full-text condition in a subselect with
"offset 0" to stop the plan reordering?
eg:
select *
from (
select * from a where comment_tsv @@ plainto_tsquery('love')
offset 0
) xx
order by timestamp DESC
limit 24
offset 0;
See ht
Krade wrote:
SELECT * FROM a WHERE comment_tsv @@ plainto_tsquery('love') ORDER BY
timestamp DESC LIMIT 24 OFFSET 0;
Have you tried make the full-text condition in a subselect with "offset
0" to stop the plan reordering?
eg:
select *
from (
select * from a where comment_tsv @@ plainto_tsq
Hello,
On 7/20/2009 22:42, Kevin Grittner wrote:
Have you considered keeping rows "narrow" until you've identified your
24 rows? Something like:
SELECT * FROM a
WHERE id in
(
SELECT id FROM a
WHERE comment_tsv @@ plainto_tsquery('love')
ORDER BY timestamp DESC
Krade wrote:
> SELECT * FROM a WHERE comment_tsv @@ plainto_tsquery('love')
> ORDER BY timestamp DESC LIMIT 24 OFFSET 0;
Have you considered keeping rows "narrow" until you've identified your
24 rows? Something like:
SELECT * FROM a
WHERE id in
(
SELECT id FROM a
WHERE co
Hello, thanks for your replies.
On 7/20/2009 13:12, Oleg Bartunov wrote:
Hmm, everything is already written in explain :) In the first query
253635 rows should be readed from disk and sorted, while in the
second query only 24 (random) rows readed from disk, so there is 4
magnitudes
difference
On Sun, Jul 19, 2009 at 12:07 AM, Krade wrote:
> archive=> explain analyze select * from a where comment_tsv @@
> plainto_tsquery('love') order by timestamp desc limit 24 offset 0;
What happens if you make it:
select * from (
select * from a where comment_tsv @@plainto_tsquery('love')
Krade,
On Sat, 18 Jul 2009, Krade wrote:
Here's a couple of queries:
archive=> explain analyze select * from a where comment_tsv @@
plainto_tsquery('love') order by timestamp desc limit 24 offset 0;
QUERY PLAN
--
Limit (cost=453248.73..453248.79 rows=24 width=281) (actual
time=18
Hello,
I'm having a bit of an issue with full text search (using tsvectors) on
PostgreSQL 8.4. I have a rather large table (around 12M rows) and want
to use full text search in it (just for one of the columns). Just doing
a plainto_tsquery works reasonably fast (I have a GIN index on the
colu
20 matches
Mail list logo
