Josh Berkus writes:
> The issue here is that the planner is capable of "pushing down" the WHERE
> criteria into the first view, but not into the second, "nested" view, and so
> postgres materializes the UNIONed data set before perfoming the join.
> Thing is, I seem to recall that this particula
Klint,
> This is how I interpret it (if anyone wants to set me straight or
> improve on it feel free)
>
> Views are implemented as rules.
>
> Rules are pretty much just a macro to the query builder. When it sees
> the view, it replaces it with the implementation of the view.
Right so far.
>
> W
On Jan 26, 2005, at 5:36 AM, Leeuw van der, Tim wrote:
Hi,
What you could do is create a table containing all the fields from
your SELECT, plus a per-session unique ID. Then you can store the
query results in there, and use SELECT with OFFSET / LIMIT on that
table. The WHERE clause for this temp
What’s the effect of different encodings on database
performance?
We’re looking to switch encoding of our database from
SQL_ASCII to UTF-8 to better handle international data. I expect that at least
90% of our data will be in the ASCII range with a few characters that need
double-byte
"Marinos J. Yannikos" <[EMAIL PROTECTED]> writes:
> according to
> http://www.postgresql.org/docs/8.0/interactive/limitations.html ,
> concurrent access to GiST indexes isn't possible at the moment. I
> haven't read the thesis mentioned there, but I presume that concurrent
> read access is also
Hi,
according to
http://www.postgresql.org/docs/8.0/interactive/limitations.html ,
concurrent access to GiST indexes isn't possible at the moment. I
haven't read the thesis mentioned there, but I presume that concurrent
read access is also impossible. Is there any workaround for this, esp.
if
pgman@candle.pha.pa.us (Bruce Momjian) wrote:
> William Yu wrote:
>> > Well, that would give you the most benefit, but the memory
>> > bandwidth is still greater than on a Xeon. There's really no
>> > issue with 64 bit if you're using open source software; it all
>> > compiles for 64 bits and you'r
On Thu, 03 Feb 2005 02:10:15 +0100, Gaetano Mendola <[EMAIL PROTECTED]> wrote:
> why the index usage is lost if used in that way ?
This is how I interpret it (if anyone wants to set me straight or
improve on it feel free)
Views are implemented as rules.
Rules are pretty much just a macro to th
Gaetano,
> I did in that way just to not use postgresql specific feature.
> I can give it a try and I let you know, however the question remain,
> why the index usage is lost if used in that way ?
Because PostgreSQL is materializing the entire UNION data set in the
subselect. What Postgres ver
Josh Berkus wrote:
Gaetano,
I have a big table with ~ 10 Milion rows, and is a very
pain administer it, so after years I convinced my self
to partition it and replace the table usage ( only for reading )
with a view.
Now my user_logs table is splitted in 4:
user_logs
user_logs_2002
user_logs_2003
Markus Schaber <[EMAIL PROTECTED]> writes:
> Tom Lane schrieb:
>> I don't buy this analysis at all. The toasted columns are not those in
>> the index (because we don't support out-of-line-toasted index entries),
>> so a WHERE clause that only touches indexed columns isn't going to need
>> to fetch
PFC wrote:
>
> contrib/intarray has an index type which could be what you need.
>
I've used intarray for a site that requires that I match multiple low
cardinality attributes with multiple search criteria. Here's an
(abridged) example:
The table:
\d person_attributes
Table
Merlin Moncure wrote:
> [...]
> > (...DBI + DBD::Pg), so that switching to 8.0 should
> automatically enable the "single-prepare, multiple-execute" behavior,
> saving a lot of query planner processing, if I understand correctly.
[...]
I know that the perl people were pushing for certain features in
Gaetano,
> I have a big table with ~ 10 Milion rows, and is a very
> pain administer it, so after years I convinced my self
> to partition it and replace the table usage ( only for reading )
> with a view.
>
> Now my user_logs table is splitted in 4:
>
> user_logs
> user_logs_2002
> user_logs_2003
> By now, our system has never used "stored procedures" approach,
> due to the fact that we're staying on the minimum common SQL features
> that are supported by most db engines.
> I realize though that it would provide an heavy performance boost.
I feel your pain. Well, sometimes you have to bi
Bruce Momjian wrote:
William Yu wrote:
You can get 64-bit Xeons also but it takes hit in the I/O department due
to the lack of a hardware I/O MMU which limits DMA transfers to
addresses below 4GB. This has a two-fold impact:
1) transfering data to >4GB require first a transfer to <4GB and then a
William Yu wrote:
> >>You can get 64-bit Xeons also but it takes hit in the I/O department due
> >>to the lack of a hardware I/O MMU which limits DMA transfers to
> >>addresses below 4GB. This has a two-fold impact:
> >>
> >>1) transfering data to >4GB require first a transfer to <4GB and then a
Hi, Tom,
Tom Lane schrieb:
>>IMHO, this tells the reason. The query planner has a table size of 3
>>pages, which clearly is a case for a seqscan. But during the seqscan,
>>the database has to fetch an additional amount of 8225 toast pages and
>>127 toast index pages, and rebuild the geometries co
You can get 64-bit Xeons also but it takes hit in the I/O department due
to the lack of a hardware I/O MMU which limits DMA transfers to
addresses below 4GB. This has a two-fold impact:
1) transfering data to >4GB require first a transfer to <4GB and then a
copy to the final destination.
2) Yo
Markus Schaber <[EMAIL PROTECTED]> writes:
> IMHO, this tells the reason. The query planner has a table size of 3
> pages, which clearly is a case for a seqscan. But during the seqscan,
> the database has to fetch an additional amount of 8225 toast pages and
> 127 toast index pages, and rebuild the
William Yu wrote:
> > Well, that would give you the most benefit, but the memory bandwidth is
> > still greater than on a Xeon. There's really no issue with 64 bit if
> > you're using open source software; it all compiles for 64 bits and
> > you're good to go. http://stats.distributed.net runs on a
[This mail goes as X-Post to both pgsql-perform and postgis-users
because postgis users may suffer from this problem, but I would prefer
to keep the Discussion on pgsql-performance as it is a general TOAST
problem and not specific to PostGIS alone.]
Hello,
Running PostGIS 0.8.1 under PostgreSQL 7
PFC wrote:
> > There's a great deal about this in the list archives (probably more in
> > pgsql-hackers than in -performance). Most of the current interest has
> > to do with building in-memory bitmaps on the fly, as a way of decoupling
> > index and heap scan processing. Which is not quite what
Andrew Sullivan wrote:
> On Wed, Jan 19, 2005 at 10:42:26AM -0500, Alan Stange wrote:
> >
> > I'm fairly sure that the pi and po numbers include file IO in Solaris,
> > because of the unified VM and file systems.
>
> That's correct.
I have seen cases on BSDs where 'pi' includes page-faulting in
Hi,
I am trying to build a function that would extend the trigger in general
tid bits that would only track count changes for table rows.
The one i am trying to build would check which column and value should
be tracked.
e.g. below would be the tracker.
CREATE TABLE "public"."" (
"tables"
25 matches
Mail list logo
