On Mon, Jan 11, 2010 at 2:41 PM, Jeremy Harris wrote:
> On 01/11/2010 02:53 AM, Robert Haas wrote:
>>
>> On Sun, Jan 10, 2010 at 9:04 AM, Jeremy Harris wrote:
>>>
>>> Needing to use an external (on-disk) sort method, when taking
>>> only 90MB, looks odd.
>
> [...]
>>
>> Well, you'd need to have w
On 01/11/2010 02:53 AM, Robert Haas wrote:
On Sun, Jan 10, 2010 at 9:04 AM, Jeremy Harris wrote:
Needing to use an external (on-disk) sort method, when taking
only 90MB, looks odd.
[...]
Well, you'd need to have work_mem> 90 MB for that not to happen, and
very few people can afford to set th
Mathieu De Zutter wrote:
> So if this query usually does *not* hit the cache, it will be
> probably faster if I leave it like that? While testing a query I
> execute it that much that it's always getting into the cache.
> However, since other applications run on the same server, I think
> that i
Postgres is being conservative. The plan it uses (bitmap index scan)
will perform much better than an index scan when the data is not in the
cache, by maybe an order of magnitude, depending on your hardware setup.
The index scan may perform better at the moment, but the bitmap index
scan is
On Mon, 11 Jan 2010, Mathieu De Zutter wrote:
> seq_page_cost = 0.1
> random_page_cost = 0.1
So if this query usually does *not* hit the cache, it will be probably faster
if I leave
it like that? While testing a query I execute it that much that it's always
getting into
the cache. However, s
On Mon, Jan 11, 2010 at 3:52 AM, Robert Haas wrote:
> On Sun, Jan 10, 2010 at 10:53 AM, Kevin Grittner
> wrote:
> > seq_page_cost = 0.1
> > random_page_cost = 0.1
>
> These might not even be low enough. The reason why bitmap index scans
> win over plain index scans, in general, is because you m
On Sun, Jan 10, 2010 at 9:04 AM, Jeremy Harris wrote:
> On 01/10/2010 12:28 PM, Mathieu De Zutter wrote:
>>
>> Sort (cost=481763.31..485634.61 rows=1548520 width=338) (actual
>> time=5423.628..6286.148 rows=1551923 loops=1)
>> Sort Key: event_timestamp
>
> > Sort Method: external merge Disk:
On Sun, Jan 10, 2010 at 10:53 AM, Kevin Grittner
wrote:
> seq_page_cost = 0.1
> random_page_cost = 0.1
These might not even be low enough. The reason why bitmap index scans
win over plain index scans, in general, is because you make one pass
through the heap to get all the rows you need instead
I wrote:
> work_mem = 32MB
Hmmm... With 100 connections and 2 GB RAM, that is probably on the
high side, at least if you sometimes use a lot of those connections
at the same time to run queries which might use sorts or hashes. It's
probably safer to go down to 16MB or even back to where you ha
Mathieu De Zutter wrote:
> Intel(R) Core(TM)2 Duo CPU E7200 @ 2.53GHz
> 2GB RAM
> 2x500GB RAID-1
> Running Debian/Etch AMD64
> PG version: PostgreSQL 8.3.8 on x86_64
> Server also runs DNS/Mail/Web/VCS/... for budget reasons.
> Database size is 1-2 GB. Also running copies of it for testing/d
On Sun, Jan 10, 2010 at 4:18 PM, Kevin Grittner wrote:
> Mathieu De Zutter wrote:
>
> You didn't include any information on your hardware and OS, which can
> be very important. Also, what version of PostgreSQL is this?
> SELECT version(); output would be good.
>
Intel(R) Core(TM)2 Duo CPU
Mathieu De Zutter wrote:
You didn't include any information on your hardware and OS, which can
be very important. Also, what version of PostgreSQL is this?
SELECT version(); output would be good.
> How can I make pgsql realize that it should always pick the index
> scan?
That would probably
On 01/10/2010 12:28 PM, Mathieu De Zutter wrote:
Sort (cost=481763.31..485634.61 rows=1548520 width=338) (actual
time=5423.628..6286.148 rows=1551923 loops=1)
Sort Key: event_timestamp
> Sort Method: external merge Disk: 90488kB
-> Seq Scan on log_event (cost=0.00..79085.92 rows=154
Hi,
Part of a larger problem, I'm trying to optimize a rather simple query which
is basically:
SELECT * FROM table WHERE indexed_column > ... ORDER BY indexed_column DESC;
(see attachment for all details: table definition, query, query plans)
For small ranges it will choose an index scan which i
14 matches
Mail list logo
