On Sat, 2005-09-17 at 17:47 +0200, Antoine Bajolet wrote:
> There are more tables around, but the heart of the search engine is
> made of three tables :
>
> fiches (f_id int4, f_title varchar) 52445 rows
> engine (f_id int4, k_id int4, weight )11761700 rows
> keywords(k_id, keyword
On Thu, 2005-09-22 at 12:21 +0800, K C Lau wrote:
> Investigating further on this problem I brought up in June, the following
> query with pg 8.0.3 on Windows scans all 1743 data records for a player:
>
> esdt=> explain analyze select PlayerID,AtDate from Player a
> where PlayerID='0' and
We use similar views as base views throughout our OLTP system to get the
latest time-based record(s). So it is quite impossible to use summary
tables etc. Are there other ways to do it?
The subquery would pinpoint the record(s) with the composite primary key.
Both MS Sql and Oracle do not have
On Thu, 2005-09-22 at 18:40 +0800, K C Lau wrote:
> We use similar views as base views throughout our OLTP system to get the
> latest time-based record(s). So it is quite impossible to use summary
> tables etc. Are there other ways to do it?
>
> The subquery would pinpoint the record(s) with the
> >I previously posted the following as a sequel to my SELECT DISTINCT
> >Performance Issue question. We would most appreciate any clue or
> >suggestions on how to overcome this show-stopping issue. We are using
> >8.0.3 on Windows.
> >
> >Is it a known limitation when using a view with SELECT ...
At 20:48 05/09/22, Simon Riggs wrote:
On Thu, 2005-09-22 at 18:40 +0800, K C Lau wrote:
> We use similar views as base views throughout our OLTP system to get the
> latest time-based record(s). So it is quite impossible to use summary
> tables etc. Are there other ways to do it?
>
> The subquery
At 22:37 05/09/22, Merlin Moncure wrote:
> >create or replace view VCurPlayer as select * from Player a
> >where a.AtDate = (select Max(b.AtDate) from Player b where a.PlayerID=
> >b.PlayerID);
Here is a trick I use sometimes with views, etc. This may or may not be
effective to solve your prob
K C Lau <[EMAIL PROTECTED]> writes:
> At 20:48 05/09/22, Simon Riggs wrote:
>> Even if this were fixed for 8.1, which seems unlikely, would you be able
>> to move to that release immediately?
> Yes. In fact when we first developed our system a few years ago, we tested
> on MS7.0, Oracle 8 and PG
On Thu, 2005-09-22 at 22:39 +0800, K C Lau wrote:
> >Is a 51ms query really such a problem for you?
>
> Unfortunately yes, as our target performance is in the high hundreds of
> transactions per sec. And 51 ms is already the best case for a single
> select, with everything cached in memory immed
Antoine Bajolet <[EMAIL PROTECTED]> writes:
> We are using postgresql in a search engine on an intranet handling
> throusand of documents.
> But we ave a big problem when users use more than two search key.
I think you need to increase the statistics targets for your keywords
table --- the estima
Hello,
Tom Lane a écrit :
Antoine Bajolet <[EMAIL PROTECTED]> writes:
We are using postgresql in a search engine on an intranet handling
throusand of documents.
But we ave a big problem when users use more than two search key.
I think you need to increase the statistics targets for
Re,
With modifing parameters like this :
ALTER TABLE keywords ALTER keyword SET STATISTICS 100;
ALTER TABLE keywords ALTER k_id SET STATISTICS 100;
ALTER TABLE engine ALTER k_id SET STATISTICS 100;
ALTER TABLE engine ALTER f_id SET STATISTICS 100;
vacuuming both tables
and rewriting the queries
> >Here is a trick I use sometimes with views, etc. This may or may not
be
> >effective to solve your problem but it's worth a shot. Create one
small
> >SQL function taking date, etc. and returning the values and define it
> >immutable. Now in-query it is treated like a constant.
>
> We don't u
Have you tried the "best choice" pattern -- where you select the set of
candidate rows and then exclude those for which a better choice
exists within the set? I often get better results with this pattern than
with the alternatives. Transmuting your query to use this patter gives:
select PlayerI
Hi,
Here is the information that you requested.
The sub query that I am using is
EXPLAIN ANALYZE SELECT doc.doc_documentid FROM document AS doc
LEFT JOIN document as root
ON doc.doc_internalRootXref = root.doc_documentId
LEFT JOIN folder_document ON doc.doc_docume
Hi,
I've got many queries running much slower on 8.1 beta2 than on 8.0.1
Here is a simplified one that takes 484 ms on 8.1 and 32 ms on 8.0.1.
select
0
from
Content C
left outer join Supplier S
on C.SupplierId = S.SupplierId
left outer join Price P
on C.PriceId = P.PriceId;
Any i
What stood out to me the most was:On Sep 22, 2005, at 2:20 PM, Jean-Pierre Pelletier wrote: -> Seq Scan on price p (cost=0.00..11317.75 rows=581475 width=4) a) is the index there, b) have you analyzed, c) perhaps the planners have different default values for when to use an index vrs a seqscan..
All indexes are there, and I've analyzed the three
tables.
I turned off seq scan, the query plans became
identical but the performance
was not better.
- Original Message -
From:
Gavin M. Roy
To: Jean-Pierre Pelletier
Cc: pgsql-performance@postgresql.org
Sent: Thu
Jean-Pierre Pelletier wrote:
> Hi,
>
> I've got many queries running much slower on 8.1 beta2 than on 8.0.1
> Here is a simplified one that takes 484 ms on 8.1 and 32 ms on 8.0.1.
>
> select
> 0
> from
> Content C
>
> left outer join Supplier S
> on C.SupplierId = S.SupplierId
>
> le
Here are the explain analyze:
On 8.1 beta2:
"Nested Loop Left Join (cost=0.00..18591.77 rows=1 width=0) (actual
time=1320.302..2439.066 rows=1 loops=1)"
" Join Filter: ("outer".priceid = "inner".priceid)"
" -> Nested Loop Left Join (cost=0.00..5.59 rows=1 width=4) (actual
time=0.044..0.0
Jean-Pierre Pelletier wrote:
> Here are the explain analyze:
What is the explain analyze if you use "set enable_seqscan to off"?
Also, can you post the output of:
\d supplier
\d price
\d content
Mostly I just want to see what the indexes are, in the case that you
don't want to show us your schem
Jean-Pierre,
First off, you're on Windows?
> " -> Seq Scan on price p (cost=0.00..11317.75 rows=581475 width=4)
> (actual time=0.004..1143.720 rows=581475 loops=1)"
Well, this is your pain point. Can we see the index scan plan on 8.1?
Given that it's *expecting* only one row, I can't unde
With enable-seq-scan = off, it runs in 350 ms so better than 484 ms
but still much slower than 32 ms in 8.0.1.
==
Table "public.content"
Column | Type | Modifiers
+-+---
contentid | integer | not null
supplierid |
Have tried adjusting the effective_cache_size so that you don't the
planner may produce a better explain plan for you and not needing to set
seqscan to off.
--
Husam
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Jean-Pierre
Pelletier
Sent: Thursda
- Original Message -
From: "Jean-Pierre Pelletier" <[EMAIL PROTECTED]>
To:
Sent: Thursday, September 22, 2005 6:37 PM
Subject: Re: [PERFORM] Queries 15 times slower on 8.1 beta 2 than on 8.0
How do I produce an "Index scan plan" ?
- Original Message -
From: "Josh Berkus"
T
On Thu, Sep 22, 2005 at 03:19:05PM -0700, Josh Berkus wrote:
> > " -> Seq Scan on price p (cost=0.00..11317.75 rows=581475 width=4)
> > (actual time=0.004..1143.720 rows=581475 loops=1)"
>
> Well, this is your pain point. Can we see the index scan plan on 8.1?
> Given that it's *expecting*
I don't know if it makes a difference but in my tables,
content.supplierid and content.priceid were nullable.
- Original Message -
From: "Michael Fuhr" <[EMAIL PROTECTED]>
To: "Josh Berkus"
Cc: ; "Jean-Pierre Pelletier"
<[EMAIL PROTECTED]>; "John Arbash Meinel" <[EMAIL PROTECTED]>
Se
my settings are:
effective_cache_size = 1000
random_page_cost = 4
work_mem = 2
- Original Message -
From: "Josh Berkus"
To: "Jean-Pierre Pelletier" <[EMAIL PROTECTED]>
Sent: Thursday, September 22, 2005 6:58 PM
Subject: Re: [PERFORM] Queries 15 times slower on 8.1 beta 2 than on 8
Michael Fuhr <[EMAIL PROTECTED]> writes:
> I've created a simplified, self-contained test case for this:
I see the problem --- I broke best_inner_indexscan() for some cases
where the potential indexscan clause is an outer-join ON clause.
regards, tom lane
Jean-Pierre,
> effective_cache_size = 1000
Try setting this to 16,384 as a test.
> random_page_cost = 4
Try setting this to 2.5 as a test.
> work_mem = 2
--
--Josh
Josh Berkus
Aglio Database Solutions
San Francisco
---(end of broadcast)--
On Thu, Sep 22, 2005 at 07:07:41PM -0400, Jean-Pierre Pelletier wrote:
> I don't know if it makes a difference but in my tables,
> content.supplierid and content.priceid were nullable.
That makes no difference in the tests I've done.
Tom Lane says he's found the problem; I expect he'll be committ
The recommendation for effective_cache_size is about 2/3 of your
server's physical RAM (if the server is dedicated only for postgres).
This should have a significant impact on whether Postgres planner
chooses indexes over sequential scans.
--
Husam
-Original Message-
From: [EMAIL P
Jean-Pierre,
> Thanks everybody for your help, I'll be awaiting the fix.
>
> I've also noticed that pg_stat_activity is always empty even if
> stats_start_collector = on
Yes, I believe that this is a know Windows issue. Not sure if it's fixed
in 8.1.
--
--Josh
Josh Berkus
Aglio Database Sol
Thanks everybody for your help, I'll be awaiting the fix.
I've also noticed that pg_stat_activity is always empty even if
stats_start_collector = on
- Original Message -
From: "Michael Fuhr" <[EMAIL PROTECTED]>
To: "Jean-Pierre Pelletier" <[EMAIL PROTECTED]>
Cc: "Josh Berkus" ; ;
"Joh
Michael Fuhr <[EMAIL PROTECTED]> writes:
> Tom Lane says he's found the problem; I expect he'll be committing
> a fix shortly.
The attached patch allows it to generate the expected plan, at least
in the test case I tried.
regards, tom lane
*** src/backend/optimizer/path/i
Hello Tom,
Thanks a lot for your quick response. Which version do you think is the
more stable one that we should upgrade to?
Please provide us with the Upgrade instructions/documentation to be
followed for both red hat and PostgreSQL.
Thanks and Best Regards,
Anu
-
Explain analyze on my 8.0.1 installation does report the time for
slower queries but for this small query it reports 0.000 ms
- Original Message -
From: "Josh Berkus"
To:
Cc: "Jean-Pierre Pelletier" <[EMAIL PROTECTED]>; "John Arbash
Meinel" <[EMAIL PROTECTED]>
Sent: Thursday, Septem
Anu,
> Thanks a lot for your quick response. Which version do you think is the
> more stable one that we should upgrade to?
8.0.3
> Please provide us with the Upgrade instructions/documentation to be
> followed for both red hat and PostgreSQL.
See the PostgreSQL documentation for upgrade instru
I have a table that is purged by 25% each night. I'd like to do a
vacuum nightly after the purge to reclaim the space, but I think I'll
still need to do a vacuum full weekly.
Would there be any benefit to doing a cluster instead of the vacuum?
---(end of broadcast)--
I have about 419804 rows in my article table. I have
installed tsearch2 and its gist index correctly.
My table structure is:
CREATE TABLE tbarticles
(
articleid int4 NOT NULL,
title varchar(250),
mediaid int4,
datee date,
content text,
contentvar text,
mmcol float4
Hello!
Got a DB with traffic statictics stored. And a SELECT statement which
shows traffic volume per days also divided by regions - local traffic and
global.
Thus SELECT statement returns about some (in about 10-20) rows paired
like this:
ttype (text)| volume (int)| tdate (date
41 matches
Mail list logo
