On Feb 24, 2011, at 14:55, Dave Crooke wrote:
> Is there a more elegant way to write this, perhaps using PG-specific
> extensions?
SELECT DISTINCT ON (data.id_key)
data.id_key, data.time_stamp, data.value
FROM data
ORDER BY data.id_key, data.time_stamp DESC;
Michael Glaeseman
read much of the rest of this thread, so others may have brought
these up before.
Michael Glaesemann
grzm seespotcode net
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
. Rtfm!.
AIUI, rownum applies numbering to output rows in a SELECT statement,
rather than some actual column of the table, which is likely what the
OP is getting at.
http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html
Michael Glaesemann
grzm seespotcode net
--
Sent via pg
troubleshoot your problems far more
rapidly.
Can something similar be added to the footer of (at least) the
performance list?
Michael Glaesemann
grzm seespotcode net
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http
ather
than actively exploring new, potential features.
Michael Glaesemann
grzm seespotcode net
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
/loops bit (which is why explain-
analyze.info times and percentages are currently miscalculated). I
took startup time to be the time to return the first row *of the
first loop*. But it's actually the average startup time to return the
first row *in each loop*, right?
Michael Glaesemann
452 ms - 12.700 ms = 1.752 ms: 12%
Is this correct?
Michael Glaesemann
grzm seespotcode net
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
our
server configuration.
Unfortunately I don't have the time to look at the query plan in more
detail, but I suspect there's a better way to get the results you're
looking for.
Michael Glaesemann
grzm seespotcode net
---(end of broadcast)-
(z = b) OR ... or somehow add it to the join list, so
performance will vary.
Michael Glaesemann
grzm seespotcode net
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if yo
processing in postgres is SLOW.
Um, what array processing are you seeing here? IN (a, b, b) is not an
array construct.
Michael Glaesemann
grzm seespotcode net
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
h for your system, so it's never able to catch up.
Without knowing details it's hard to say for certain. What are your
autovacuum settings and other details about the load on your system?
Michael Glaesemann
grzm seespotcode net
On Jul 17, 2007, at 15:50 , Thomas Finneid wrote:
Michael Glaesemann wrote:
2a) Are you using INSERT INTO foo (foo1, foo2, foo2) SELECT foo1,
foo2, foo3 FROM pre_foo or individual inserts for each row? The
former would be faster than the latter.
performed with JDBC
insert into
r.
2b) If you are doing individual inserts, are you wrapping them in a
transaction? The latter would be faster.
Michael Glaesemann
grzm seespotcode net
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
should execute pretty quickly. You don't need to loop over any
results. Remember, SQL is a set-based language, so if you can pose
your question in a set-based way, you can probably find a pretty
good, efficient solution.
Michael Glaesemann
grzm seespotcode net
[Please don't top post as it makes the discussion more difficult to
follow.]
On Jun 22, 2007, at 16:25 , Tom Tamulewicz wrote:
The explain is as follows...
EXPLAIN ANALYZE, please. (And for convenience, it helps if you
include the query :) )
Michael Glaesemann
grzm seespotcod
reparse and
replan the query. Of course, if you change the arguments, it can't
use the result that's cached from the previous run.
Take this all with an appropriate amount of salt. I'm learning about
this, too.
Michael Glaesemann
grzm seespotcode net
--
ating that the
query will return 13 rows, but you're actually returning 539. Maybe
there's some corruption in the index which is leading to both the
performance issue you're seeing and the statistics issues. Have you
tried REINDEX?
Michael Glaesemann
x and looking up the corresponding row. If you want
to test this, you can set enable_seqscan to false and try running
your query again.
http://www.postgresql.org/docs/8.2/interactive/runtime-config-
query.html#RUNTIME-CONFIG-QUERY-ENABLE
Michael Glaesemann
grzm seespotcod
e's a performance difference.
The weird thing is that on 8.2, I don't see any sequential scans
taking place, it seems to be properly using the indexes.
As an aside, whether the planner decides to use a sequential scan or
an index has more to do with the particular query: indexes a
id column value for each row in the table. The
fastest way to do this is visiting every row., i.e., a sequential
scan. Using an index would require (1) looking in the index and (2)
looking up the corresponding row.
Michael Glaesemann
grzm seespotcode net
---(end of
an ANALYZE?
Your row estimates look off by a couple orders of magnitude. With up-
to-date statistics the planner might do a better job.
As for any other improvements, I'll leave that to those that know
more than I. :)
Michael Glaesemann
grzm seespotcode net
---
routine maintenance).
Michael Glaesemann
grzm seespotcode net
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
tical tasks for your current 8.1.3 installation and then compare
the results with those same benchmarks run against 8.2.
Michael Glaesemann
grzm seespotcode net
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donati
On Jan 18, 2007, at 13:56 , Michael Glaesemann wrote:
On Jan 18, 2007, at 13:43 , Gauri Kanekar wrote:
Can anybody suggest some comprehensive test for version change
from 8.1.3 to 8.2
http://www.postgresql.org/docs/8.2/interactive/release.html
Sorry, I misread your request as a list of
On Jan 18, 2007, at 13:43 , Gauri Kanekar wrote:
Can anybody suggest some comprehensive test for version change from
8.1.3 to 8.2
http://www.postgresql.org/docs/8.2/interactive/release.html
Michael Glaesemann
grzm seespotcode net
---(end of broadcast
ould think.
Michael Glaesemann
grzm seespotcode net
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
r_index on foos(bar) where bar <> 0;
Take a look on the docs on partial indexes for more information.
http://www.postgresql.org/docs/current/interactive/indexes-partial.html
Hope this helps.
Michael Glaesemann
grzm seespotcode net
---(end of broadcast)
side of the list
entirely, so any returned mail is also outside of the list. I've seen
this happen occasionally myself. Could this be what you're seeing?
AFAICT, such messages sent to the list *do* get filtered out.
Michael Glaesemann
grzm seespotcode net
it can do what
you're looking for.
Michael Glaesemann
grzm seespotcode net
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
e list emails? Now *that'd* be sweet.)
Michael Glaesemann
grzm seespotcode net
---(end of broadcast)---
TIP 6: explain analyze is your friend
I have got such problem.
Im running Postgresql 7.3.2 on Linux 2.6.13.
Also, you should seriously consider upgrading. 8.1.3 is the current
PostgreSQL release. If you must remain on 7.3, at least upgrade to
7.3.14, which contains many bugfixes.
Michael Glaesemann
grzm seespotcode net
I have got such problem.
Im running Postgresql 7.3.2 on Linux 2.6.13.
Also, you should seriously consider upgrading. 8.1.3 is the current
PostgreSQL release. If you must remain on 7.3, at least upgrade to
7.3.14, which contains *many* bugfixes.
Michael Glaesemann
grzm seespotcode net
eople. For
more specific advice (e.g., for your particular situation), it would
be very helpful if you could provide examples of queries that aren't
performing well for you (including table schema and explain analyze
output).
Michael Glaesemann
grzm myr
on to your database-
level checks.
Michael Glaesemann
grzm myrealbox com
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
(
SELECT *
FROM table
ORDER eventtime DESC
LIMIT 1000
) as most_recent_1000
Don't know enough about the other parts, but hopefully this can get
you started. :)
Michael Glaesemann
grzm myrealbox com
---(end of broa
higher[2].
[1](http://www.postgresql.org/docs/current/interactive/sql-set.html)
[2](http://www.postgresql.org/docs/current/interactive/runtime-config-
logging.html#RUNTIME-CONFIG-LOGGING-WHEN)
Michael Glaesemann
grzm myrealbox com
---(end of broadcast)-
planning. VACUUM alone does not
do this.
Do you have an index on report.id_order ? Try creating an index for
it if not and run a vacuum analyze on the table to see if it gets
rid of the sequence scan in the plan.
Michael Glaesemann
grzm myrealbox com
---(end of
your hardware.
Michael Glaesemann
grzm myrealbox com
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through t
l.org/docs/8.0/interactive/functions-
datetime.html#FUNCTIONS-DATETIME-CURRENT>
Michael Glaesemann
grzm myrealbox com
---(end of broadcast)---
TIP 8: explain analyze is your friend
ffset for the appropriate timestamptz values.
There has been discussion in the past on storing the time zone name
with the timestamptz as well, though no one has implemented this yet.
Michael Glaesemann
grzm myrealbox com
---(end of broadcast)---
TIP
On Nov 5, 2004, at 5:14 PM, Michael Glaesemann wrote:
On Nov 5, 2004, at 4:16 PM, Antony Paul wrote:
where today::date = '2004-11-05';
This is the only condition in the query. There is a btree index on the
column today.
Is there any way to optimise it.
I'm sure others out there ha
ent_date = date '2004-11-05'
Might not make a difference at all, but perhaps PostgreSQL is coercing
both values to timestamp or some other type as you're only providing a
string to compare to a date. Then again, it might make no difference at
all.
My 1 cent.
Michael
is compare to DB2 partitioning?
Michael Glaesemann
grzm myrealbox com
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
1 | 12345678911234 | 012345678911234
(1 row)
I would do as another poster suggested: create a telephone number
domain as text with the check constraints you desire.
Michael Glaesemann
grzm myrealbox com
---(end of broadcast)---
TIP 7: don't forget to inc
44 matches
Mail list logo
