On 04/04/14 17:14, Kohler Manuel (ID SIS) wrote:
> Here is the same query with fdw:
>
> db=# EXPLAIN VERBOSE select ds.code, count(*), sum(dsf.size_in_bytes) as
> "raw_size",pg_size_pretty(sum(dsf.size_in_bytes)) as "size" from
> data_set_files_fdw dsf, data_sets_fdw ds where dsf.parent_id is nu
"Kohler Manuel (ID SIS)" writes:
> here are the Query plans. The first plan is on the source database directly.
> So no fdw involved:
Do you have use_remote_estimate enabled? The rowcount estimates for the
foreign tables seem pretty far from reality; use_remote_estimates should
help.
Hi,
here are the Query plans. The first plan is on the source database directly. So
no fdw involved:
source_db=# EXPLAIN ANALYZE select ds.code, count(*), sum(dsf.size_in_bytes) as
"raw_size",pg_size_pretty(sum(dsf.size_in_bytes)) as "size" from data_set_files
dsf, data_sets ds where dsf.parent
Kohler Manuel wrote:
> I have a question regarding the use of indices when querying foreign data
> tables (using postgres_fdw
> of 9.3 to another postgres DB).
> Everything works fine beside the fact that the indices which are defined in
> the foreign DB are not
> used at all when I do query it t
Hi,
I have a question regarding the use of indices when querying foreign data
tables (using postgres_fdw of 9.3 to another postgres DB).
Everything works fine beside the fact that the indices which are defined in the
foreign DB are not used at all when I do query it through the foreign data
wrap
Yeah, but reply-all will still send private messages :O .. its strange
because this is the only mailing list that gmail behaves like this with.. it
must have to with how postgre sends messages out.
But anyways. Back to topic :)
Yeah, I think that a partial index is something that would be smart. T
Mohamed writes:
> My Gmail(bloody gmail!) has been auto-replying to the last messager (Scott)
> so I think we have been having a private discussion on this topic.
There is an option in the Google Labs tab to make "Reply All" the default
button -- of course then there's always a chance you'll ma
On Sun, Feb 01, 2009 at 06:00:02PM +0100, Mohamed wrote:
> When it comes to the boolean, the content is about 70-30%. I find it strange
> though that an index on a 50-50% isn't that useful. With an index the DB can
> skip 50% of the table so it should be useful, but perhaps the intersection
> of se
My Gmail(bloody gmail!) has been auto-replying to the last messager (Scott)
so I think we have been having a private discussion on this topic. Here is
an update on our discussion.
ME :
When it comes to the boolean, the content is about 70-30%. I find it strange
though that an index on a 50-50% isn
On Sat, Jan 31, 2009 at 1:33 PM, Gregory Stark wrote:
> No index is going to be particularly effective for boolean columns unless
> they're very heavily skewed. You might find it useful to build separate
> partial indexes on other keys for each value though.
Not entirely true. If you've got a ta
Gregory Stark wrote:
Mohamed writes:
I want to match against a boolean field, that is, only true or false is
possible. I am thinking Btree but not sure.. correct?
No index is going to be particularly effective for boolean columns unless
they're very heavily skewed. You might find it
Mohamed writes:
> Hi,
> I have several fields that use to match with my queries. I am curious to
> what index types is best for what. Here is some examples that will help you
> understand.
>
> Say I have a 1000 000 rows.
>
> Speed is of the essence here, insertions and updates happens relatively
Hi,
I have several fields that use to match with my queries. I am curious to
what index types is best for what. Here is some examples that will help you
understand.
Say I have a 1000 000 rows.
Speed is of the essence here, insertions and updates happens relatively less
frequent than search.
I wa
Bruce Momjian writes:
>> On Fri, Dec 23, 2005 at 11:04:50AM -0500, Tom Lane wrote:
>>> It's not that easy --- in the MVCC world there simply isn't a unique
>>> count that is the right answer for every observer. But the idea of
>>> packaging a count(*) mechanism as an index type seems like it migh
Martijn van Oosterhout wrote:
-- Start of PGP signed section.
> On Fri, Dec 23, 2005 at 11:04:50AM -0500, Tom Lane wrote:
> > It's not that easy --- in the MVCC world there simply isn't a unique
> > count that is the right answer for every observer. But the idea of
> > packaging a count(*) mechani
Martijn van Oosterhout writes:
> AFAICS two big problems with using an index type:
> 1. The index isn't told when the tuple is deleted.
Hm, good point ... we could make it do so but for ordinary deletes it'd
be a waste of cycles to open indexes at all.
> 2. The server expects to be able to look
On Fri, Dec 23, 2005 at 11:04:50AM -0500, Tom Lane wrote:
> It's not that easy --- in the MVCC world there simply isn't a unique
> count that is the right answer for every observer. But the idea of
> packaging a count(*) mechanism as an index type seems like it might be
> a good one. I don't thin
Peter Eisentraut <[EMAIL PROTECTED]> writes:
> One way to conceptually tackle this count(*) issue would be to create a new
> index type for it. The index type would (logically) just need to implement
> insert and delete operations and keep a running count with a big lock around
> it. Users cou
One way to conceptually tackle this count(*) issue would be to create a new
index type for it. The index type would (logically) just need to implement
insert and delete operations and keep a running count with a big lock around
it. Users could then choose to trade off concurrent performance ag
>
> I wouldn't mind a "with visibility" switch for indexes that you could
> throw when creating them for this purpose. But burdening all indexes
> with this overhead when most wouldn't need it is not, IMHO, a good idea.
>
that would add complexity to the index code for... just one case?
what abo
On Thu, 2005-12-22 at 09:33, Jim C. Nasby wrote:
> On Thu, Dec 22, 2005 at 04:10:50PM +0100, Martijn van Oosterhout wrote:
> > Actually, ISTM the trend is going the other way. MySQL has instant
> > select count(*), as long as you're only using ISAM. Recent versions of
>
> No comment.
>
> > MSSQL
"Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> I didn't think the method of adding the imperfect known_visible bit to
> the indexes had that much overhead, but it's been a while since those
> discussions took place. I do recall some issue being raised that will be
> very difficult to solve (though
On Thu, Dec 22, 2005 at 04:10:50PM +0100, Martijn van Oosterhout wrote:
> Actually, ISTM the trend is going the other way. MySQL has instant
> select count(*), as long as you're only using ISAM. Recent versions of
No comment.
> MSSQL use an MVCC type system and it also scans the whole table. Orac
On Thu, Dec 22, 2005 at 08:52:08AM -0600, Jim C. Nasby wrote:
> Back when I was using other databases more often, it wasn't uncommon to
> see a 10x speed improvement on count(*) from using an index. This is an
> area where PostgreSQL is seriously behind other databases. Of course
> having vastly su
On Wed, Dec 21, 2005 at 04:54:08PM -0500, Greg Stark wrote:
> MSSQL presumably has the entire table cached in RAM and postgres doesn't. Even
> if MSSQL can scan just the index (which postgres can't do) I would only expect
> a factor of 2-4x. Hm. Unless perhaps this table is extremely wide? How larg
[EMAIL PROTECTED] (Marcus Engene) writes:
> Greg Stark wrote:
>> Alexander Scholz <[EMAIL PROTECTED]> writes:
>>
>>>Hi, thank you for your answer.
>>>
>>>Regarding the performance flow when trying to find out how many records are
>>>currently being stored in the table, I don't see how an index shou
On 12/21/05, Marcus Engene <[EMAIL PROTECTED]> wrote:
> Greg Stark wrote:
> > Alexander Scholz <[EMAIL PROTECTED]> writes:
> >
> >>Hi, thank you for your answer.
> >>
> >>Regarding the performance flow when trying to find out how many records are
> >>currently being stored in the table, I don't see
Greg Stark wrote:
Alexander Scholz <[EMAIL PROTECTED]> writes:
Hi, thank you for your answer.
Regarding the performance flow when trying to find out how many records are
currently being stored in the table, I don't see how an index should help...
Nevertheless we've created an unique index on "
On 12/21/05, Alexander Scholz <[EMAIL PROTECTED]> wrote:
> Regarding the performance flow when trying to find out how many records
> are currently being stored in the table, I don't see how an index should
> help... Nevertheless we've created an unique index on "ID" but SELECT
> count("ID") from "
Alexander Scholz <[EMAIL PROTECTED]> writes:
> Hi, thank you for your answer.
>
> Regarding the performance flow when trying to find out how many records are
> currently being stored in the table, I don't see how an index should help...
> Nevertheless we've created an unique index on "ID" but SE
Am Mittwoch, 21. Dezember 2005 12:01 schrieb Alexander Scholz:
> So - what kind of indexing would speed this up then?
You can't speed up a full-table count using an index.
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
Hi, thank you for your answer.
Regarding the performance flow when trying to find out how many records
are currently being stored in the table, I don't see how an index should
help... Nevertheless we've created an unique index on "ID" but SELECT
count("ID") from "XYZ" still takes 35 seconds*.
On Tue, 05 Aug 2003 18:46:47 -0400 Tom Lane <[EMAIL PROTECTED]> wrote:
> Richard Welty <[EMAIL PROTECTED]> writes:
> > hmmm. where should i go looking for sample code implementing operator
> > classes as an example?
>
> contrib. Specifically, I'd suggest looking at contrib/seg, which
> defines a
Richard Welty <[EMAIL PROTECTED]> writes:
> what type of index is recommended when the dominant lookup on a field is
> '' << ipblock
I don't think we have a suitable index type at the moment; or more
accurately, we don't have a suitable operator class. It would be an
interesting exercise to write
On Tue, 05 Aug 2003 15:09:00 -0400 Tom Lane <[EMAIL PROTECTED]> wrote:
> Richard Welty <[EMAIL PROTECTED]> writes:
> > what type of index is recommended when the dominant lookup on a field
> is
> > '' << ipblock
>
> I don't think we have a suitable index type at the moment; or more
> accurately,
in looking over the section of the users manual on indices, i see that
R-Tree indices are recommended for <<, but this advice is clearly tied to
the geometric interpretation of << ("to the left of") rather than the
network interpretation ("is contained in").
what type of index is recommended when
hello all,
we are running PostgreSQL 7.0.2 on Solaris 2.6,/x86 compiled by gcc 2.8.1.
For installation Postgres on 70 Solaris-2.6 server we built packages
(SVR4-style).
The first time we installed Postgres everything was ok. But after a new
installation of the package ( - same Postgres version
hello all,
we are running PostgreSQL 7.0.2 on Solaris 2.6,/x86 compiled by gcc 2.8.1.
For installation Postgres on 70 Solaris-2.6 server we built packages
(SVR4-style).
The first time we installed Postgres everything was ok. But after a new
installation of the package ( - same Postgres version!
Hi,
I had learned in theory that Hash indices are used for "=" and
B-tree for "<" ,">".
explain command doesn't tell us which index it is using. Hash or
Btree?
Also,
should a following query
"id < 1243" invoke a index ? (assuming there is an index on id).
I have seen Postgres
I have unfortunately deleted a message to pgsql-general today which
contained a query like:
SELECT tab1.b, tab2.c FROM tab1, tab2 WHERE tab1.a=tab2.a;
There was also a UNION following, but my memory fails me. My question is
though, can an index be used for the above query? When I try it with an
I am trying to optimise a query which looks like:
select prod_base.*, manu_base.name from prod_base, manu_base where
prod_base.mid=manu_base.mid;
manu_base is a table consisting of 3000 manufacturer with an id (not
unique to support synonyms) and a name (declared as varchar(32)).
prod_base is a t
41 matches
Mail list logo
