[PERFORM] strange query plan with LIMIT

loops=1) -> Index Scan using tdiag_pkey on tdiag (cost=0.00..19114765.76 rows=1141019 width=114) (actual time=90344.380..90344.380 rows=1 loops=1) Filter: ((create_time >= '2011-06-03 19:49:04+10'::timestamp with time zone) AND (create_time < '2011-06-06 19

[PERFORM] strange query plan with LIMIT

(cost=0.00..19114765.76 rows=1141019 width=114) (actual time=90344.380..90344.380 rows=1 loops=1) Filter: ((create_time >= '2011-06-03 19:49:04+10'::timestamp with time zone) AND (create_time < '2011-06-06 19:59:04+10'::timestamp with time zone)) Total

Re: [PERFORM] strange query plan with LIMIT

gt; sort" to "quick sort"). Try something like work_mem=20MB and see if it > does the trick. This certainly speeds up the sorting. > > regards > Tomas -- Anthony Shipman | What most people think about anthony.ship...@symstream.com | most things is mostly wrong. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] strange query plan with LIMIT

execute 'create temporary table tt on commit drop as ' || 'select diag_id from tdiag ' || v_where; query = 'select * from tdiag where diag_id in (select * from tt) ' || 'order by diag_id ' || v_limit || ' ' || v_o

Re: [PERFORM] strange query plan with LIMIT

On Wednesday 08 June 2011 18:39, Pavel Stehule wrote: > if you use FOR statement, there should be a problem in using a > implicit cursor - try to set a GUC cursor_tuple_fraction to 1.0. Alas this is mammoth replicator, equivalent to PG 8.3 and it doesn't have that parameter. -- Anth

Re: [PERFORM] strange query plan with LIMIT

ext | tag | character varying| not null default ''::character varying Indexes: "tdiag_pkey" PRIMARY KEY, btree (diag_id) "tdiag_create_time" btree (create_time) -- Anthony Shipman | Programming is like sex: One

Re: [PERFORM] strange query plan with LIMIT

On Thursday 09 June 2011 16:04, anthony.ship...@symstream.com wrote: > I must be doing something really wrong to get this to happen: Yes I did. Ignore that. -- Anthony Shipman | flailover systems: When one goes down it anthony.ship...@symstream.com | flails about until

Re: [PERFORM] strange query plan with LIMIT

ary key to (diag_id, create_time) would simple queries on diag_id still work well i.e. select * from tdiag where diag_id = 1234; -- Anthony Shipman | -module(erlang). anthony.ship...@symstream.com | ''(_)->0. %-) -- Sent via pgsql-performance mailing list (pg

[PERFORM] overzealous sorting?

: ((classification)::text = 'cdr'::text) -> Bitmap Index Scan on tevent_cdr_timestamp (cost=0.00..57.31 rows=2477 width=0) (actual time=0.404..0.404 rows=2480 loops=1) Index Cond: (("timestamp" >= '2011-09-09 22:00:00+10'::timestamp w

Re: [PERFORM] overzealous sorting?

ntages of b-tree indexes described in the section "Indexes and ORDER BY" of the manual. If I do "set enable_bitmapscan = off;" the query runs a bit faster although with a larger time range it reverts to a sequential scan. -- Anthony Shipman | Consider the set

Re: [PERFORM] overzealous sorting?

The query that I've shown is one of a sequence of queries with the timestamp range progressing in steps of 1 hour through the timestamp range. All I want PG to do is find the range in the index, find the matching records in the table and return them. All of the planner's cleverness jus

Re: [PERFORM] overzealous sorting?

t found them to be faster than reading in slices, in the tests I've done. Anyway at the moment it is fast enough. Thanks -- Anthony Shipman | It's caches all the way anthony.ship...@symstream.com | down. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance