Re: [GENERAL] custom session variables?
Hello
You could just use temporary tables like:
BEGIN;
CREATE TEMPORARY TABLE csid (i int);
-- somehow write the ID you want into that table
-- and then...
INSERT INTO other_table (changeset_ids, msg) VALUES
((SELECT i FROM csid), 'Some log message');
COMMIT;
When inserting a new changeset, you fill the temporary table
afterwards with curval('changeset_id_seq'). If you just want
to add more referring lines, you seem to know the ID already
can can just insert it. In any case, the following INSERTs into
the other tables will look exactly the same, which seem to
be your goal.
bye,
-christian-
Am Wed, 24 Apr 2013 16:17:58 -0700
schrieb Darren Duncan dar...@darrenduncan.net:
Hello,
I would like to have one or more session-scoped global variables that
are useable in a similar way to sequence generators, via analogies to
setval()+currval().
Here's a (simplified) scenario ...
Say that for auditing purposes all regular database tables have a
changeset_id column, which is a foreign key into a changesets table
that has extra columns like when_occurred and who_did_it and
purpose_of_changes etc. There is a sequence generator
changeset_id_gen that is typically used to generate the changeset_id
values.
During typical day to day use, when a set of data manipulation work
is done to enact some application task, a changesets record is added
with a newly generated changeset_id, and that changeset_id then used
in the other records added/updated/deleted (there are also
audit/history tables) to associate everything that was done as a
logical unit for some task.
So in the changesets table we have this as its pk:
changeset_id integer not null default
nextval('changeset_id_gen'::regclass)
... and in the other tables we have this as a non-pk field:
changeset_id integer not null default
currval('changeset_id_gen'::regclass)
... or there may also be a trigger to similar effect of the latter,
so it is changed for a record update too.
Now I understand that within a particular database session currval
will fail if nextval or setval weren't called on that sequence
generator yet. And so I depend on this behavior to enforce a general
business rule that a changesets record has to be inserted before
other changes in the current session.
However, under some circumstances, we may want alternately to
associate some regular changes with a prior changesets record, or
otherwise with some changesets record whose changeset_id didn't come
from the sequence generator.
To make the database simpler or cut down on verbosity, I would like
in those cases to effectively setval() changeset_id_gen at the start
of that changeset to some explicit value, so subsequent changes in
that session can just use that value instead of some newly generated
one.
Now here's the main point of this message ...
When I effectively setval() in the session, I want that change to
only be visible in the session and have no effect outside of it, such
as affecting what nextval() produces in other sessions.
It seemed to me that the proper way to do this would be to have some
other session scope variable, say changeset_id_to_use, that is an
intermediary between the sequence generator and nearly all the code
using it.
So at the start of a session, changeset_id_to_use would be manually
set by the application, either from nextval(changeset_id_gen) or from
an explicit value, and then the table default defs or triggers would
read changeset_id_to_use rather than reading
currval(changeset_id_gen).
The changeset_id_to_use should start off null/missing at the start of
the session so code that uses it will fail per business rules without
it being set first.
I would like to know the proper/best way to declare and access the
aforementioned changeset_id_to_use variable?
I found a 2009 blog post
http://frefo.blogspot.ca/2009/04/session-variables-in-postgresql.html
which looks relevant to what I want to do, but that looks more like
abuse of the system rather than using it as intended, though I could
be wrong. Is there are better solution than that or what should I be
doing?
Thank you in advance.
-- Darren Duncan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Queries seldomly take 4s while normally take 1ms?
Hello I have a setup with one master and two slaves which are used by a closed source application. The database is asked the same query, a stored procedure, with different parameters about 4 million times per second at a peak rate of 150 times per second using 10 parallel connections. The slaves are decent Dell servers with 64GB RAM with dual hexacore CPUs and RAID10. Usually this stored procedure takes 1ms as it basically just does two selects against a GIST index for a prefix_range type. Seldomly though, about 1-3 times per day, one of these queries takes up to 4000ms! All those queries also runs in 1ms when executed manually some times later. Queries with similar parameters (who I supposed to use the same area of the index) also continues to run fast during that time. Queries with different paramers which are running parallel on different threads take 1ms, too, so it's not a general load problem. Cronjobs and other applications seem quiet during that time, there is no peak in any of our monitoring graphs. Automatic vacuum/analyze log entries on the master are not near the timestamps in question. So my problem seems not the query itself nor the way I indexed my data but what could it be? Some strange effects with streaming replication or cache invalidation? Apologies for not giving you reproducible problem but maybe you still have some ideas as I'm just curious as I've never seem such an effect during my MySQL years :-) The queries contain obvious customer data so I'm reluctant to give examples but again I doubt that an explain plan will help if only 1 out of 4E6 queries takes too long. bye, -christian- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Queries seldomly take 4s while normally take 1ms?
Hello On Tue, 9 Apr 2013 03:53:13 -0700 (PDT) Greg Williamson gwilliamso...@yahoo.com wrote: Christian -- original text snip because this POS editor won't let me properly edit postgres version ? 9.2.3 type of replication ? As written, one master does streaming replication to two slaves. changes from postgres config defaults ? max_connections = 1000 # (change requires restart) shared_buffers = 20GB # min 128kB wal_level = hot_standby # minimal, archive, or hot_standby archive_mode = on # allows archiving to be done archive_command = 'test ! -f /srv/postgresql-data/archivedir/%f cp %p /srv/postgresql-data/archivedir/%f'# command to use to archive a logfile segment max_wal_senders = 3 # max number of walsender processes hot_standby = on# on allows queries during recovery max_standby_archive_delay = 1h # max delay before canceling queries max_standby_streaming_delay = 1h# max delay before canceling queries hot_standby_feedback = on # send info from standby to prevent effective_cache_size = 1024MB log_destination = 'stderr' # Valid values are combinations of logging_collector = on # Enable capturing of stderr and csvlog log_directory = '/var/log/postgresql/' # directory where log files are written, log_filename = 'postgresql-9.2-data.log'# log file name pattern, log_file_mode = 0640# creation mode for log files, log_rotation_age = 0# Automatic rotation of logfiles will log_rotation_size = 0 # Automatic rotation of logfiles will log_min_messages = notice log_min_duration_statement = 500# -1 is disabled, 0 logs all statements log_connections = on log_disconnections = on log_duration = on log_line_prefix = '%t [%p] %u@%d ' # special values: log_statement = 'all' # none, ddl, mod, all log_timezone = 'Europe/Berlin' track_activities = on track_counts = on track_io_timing = on datestyle = 'iso, dmy' timezone = 'Europe/Berlin' lc_messages = 'en_GB.UTF-8' # locale for system error message default_text_search_config = 'pg_catalog.english' shared_preload_libraries = 'pg_stat_statements' pg_stat_statements.max = 1000 pg_stat_statements.track = all effective_cache_size is way too low, I noticed, can that be the cause? Do they happen more at peak usage, semi regularly or sporadically ? Sporadically. Not at a specific hour or minute-of-hour. Not only at peak times either. Sometimes at 05:xx in the morning, sometings at 10:xxh. Possibly some sporadic postgres process such as checkpoints of autovac processes kicking off. Do your logs show anything ? As far as I understood, Auto-Vacuum and Auto-Cleaning only happen on the master and their results get to the slaves via streaming replication. I therefore checked the master logs and, as written, all occurences of Auto-* are at least half an hour before or after. HTH, Greg W. bye, -christian- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Queries seldomly take 4s while normally take 1ms?
On Tue, 9 Apr 2013 07:25:16 -0700 (PDT) Kevin Grittner kgri...@ymail.com wrote: Christian Hammers c...@lathspell.de wrote: 9.2.3 You really need to think about 9.2.4 Real Soon Now; there's a security fix that you probably should not wait on. Is scheduled (no access from outside to that network segment at least) max_connections = 1000 # (change requires restart) shared_buffers = 20GB # min 128kB Those are both potential causes. For max_connections, see this: http://wiki.postgresql.org/wiki/Number_Of_Database_Connections Maybe you happened to have enough users hit the enter key at the same moment to cause a process holding a lock to be starved of cycles or something similar. The application connects permanently with a fixed number of only 20 connections. One problem with a large shared_buffers setting is that PostgreSQL can accumulate a very large number of dirty pages and flush them to the OS all at once. This can overwhelm the storage system and cause exactly the kind of symptoms you're seeing. I have pretty big changes during early night hours on the master and then almost only read-only accesses during the day. As checkpoint_timeout is at 5min, there should not be any significant amount of dirty pages during daytime, right? Where would I verity this, with pg_stats_bgwriter.buffers_checkpoint and the Linux I/O graphs? bye, -christian- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] rights for schema
Am Sun, 30 Dec 2012 15:54:32 +0100 schrieb Philipp Kraus philipp.kr...@flashpixx.de: Hello, can I set all rights to a schema and its content? I have different schema and in the public schema all users should be do everything (select, update, delete, call functions, etc). Also if I add a new user, the user should be get also the rights. My other schemas are only access by the database user only. How can I do this? Phil AFAIK you can't set all rights for all tables, views, functions etc. inside a schema recursively with one command. Neither can you say that, inside a specific schema, every newly created table or function should automatically get a default access list. (the template1 database can only be used to do the latter for newly created databases). What you could do is to create a group (role), put all your users in it and grant permissions to that role. Remember, though, that newly created users have to be explicitly added to that group by the admin and new tables have to be given access to that role by the admin or the creating user! bye, -christian- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] update from a csv file?
Am Fri, 28 Dec 2012 00:47:35 -0500 schrieb François Beausoleil franc...@teksol.info: Le 2012-12-27 à 09:54, Kirk Wythers a écrit : I have been using COPY FROM to do a mass import of records from CSV files into a new database. I have discover however, a small number of records ( a few thousand) in one of the files that contain new data that needs to be added to the database, but on rows that have a primary key and have already been inserted (so I can't use COPY FROM because it violates the primary key). ... You will want to COPY FROM on a new table that has the same structure: BEGIN; CREATE TEMPORARY TABLE original_table_name_temp( LIKE original_table_name INCLUDING ALL ); COPY original_table_name_temp FROM stdin; The INCLUDING ALL will also generate the same unique keys that let the COPY fail in the original table so omit that. bye, -christian- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Composite Indexes with a function and a column
Hello On Fri, 21 Dec 2012 08:46:14 -0200 Alex Pires de Camargo acama...@gmail.com wrote: Is it possible? Thanks! Why not? devel_np=# CREATE TABLE t (i int); CREATE TABLE devel_np=# CREATE INDEX ON t (length(i::text), i); CREATE INDEX bye, -christian- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Composite Indexes with a function and a column
Hallo A function that is used as part of an index has at least to be declared immutable: devel_np=# CREATE OR REPLACE FUNCTION f() RETURNS int AS $$ BEGIN return (random()*100)::int; END; $$ VOLATILE LANGUAGE plpgsql; CREATE FUNCTION devel_np=# CREATE INDEX ON t (f(), i); ERROR: functions in index expression must be marked IMMUTABLE Of couse, you can just declare your function as IMMUTABLE and still call random() or access other tables in it if you think you know what you're doing. (I wonder if it's somehow possible to get PostgreSQL into an endless loop or crash by doing an ORDER BY which uses an index that returns random values...) bye, -christian- On Fri, 21 Dec 2012 10:31:43 -0200 Alex Pires de Camargo acama...@gmail.com wrote: Thanks a lot! From documentation: IMMUTABLE indicates that the function cannot modify the database and always returns the same result when given the same argument values; that is, it does not do database lookups or otherwise use information not directly present in its argument list. If this option is given, any call of the function with all-constant arguments can be immediately replaced with the function value. I understand that to be immutable a function should not access mutable data. If my function access another table that I have guarantee that it will not be changed, It's safe to turn that function immutable and use in an index? I know that i'll be punished if my guarantee fails... Regards, On Fri, Dec 21, 2012 at 10:12 AM, Christian Hammers c...@lathspell.de wrote: Hello On Fri, 21 Dec 2012 08:46:14 -0200 Alex Pires de Camargo acama...@gmail.com wrote: Is it possible? Thanks! Why not? devel_np=# CREATE TABLE t (i int); CREATE TABLE devel_np=# CREATE INDEX ON t (length(i::text), i); CREATE INDEX bye, -christian- -- Network Engineering Design; Content Delivery Platform IP NETCOLOGNE Gesellschaft für Telekommunikation mbH Am Coloneum 9 | 50829 Köln Tel: 0221 -8711 | Fax: 0221 -78711 www.netcologne.de Geschäftsführer: Dr. Hans Konle (Sprecher) Dipl.-Ing. Karl-Heinz Zankel HRB 25580, AG Köln Diese Nachricht (inklusive aller Anhänge) ist vertraulich. Sollten Sie diese Nachricht versehentlich erhalten haben, bitten wir, den Absender (durch Antwort-E-Mail) hiervon unverzüglich zu informieren und die Nachricht zu löschen. Die E-Mail darf in diesem Fall weder vervielfältigt noch in anderer Weise verwendet werden. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Monitoring streaming replication from standby on Windows
Hello I was just wondering the same. As for the slave, it seems to me that the ps fax output postgres: startup process recovering is at least a quick way to check if the replication client has synced. In a case where e.g. the slave was down for a while and then unsuccessfully waits for WAL files which were long deleted on the master, this line changed to startup process waiting. HTH, -christian- On Fri, 14 Dec 2012 09:52:49 -0400 Yamen LA iya...@live.com wrote: Thank you Karl. I am trying to determine on the slave itself whether streaming replication (i.e. WAL receiver process) is active or not, similar to checking pg_stat_replication on the master. In fact, this is part of a larger module I am building to control the databases and automate failovers. As for monitoring the offset between the two, what is a reasonable value for the differences between last xlog sent, received and replayed? -Yamen Date: Thu, 13 Dec 2012 19:43:53 -0600 From: k...@denninger.net To: iya...@live.com CC: pgsql-general@postgresql.org Subject: Re: [GENERAL] Monitoring streaming replication from standby on Windows On 12/13/2012 7:36 PM, Yamen LA wrote: Hello, I would like to know how to check the status of the streaming replication from standby server on Windows. Apparently from the master I can use the pg table pg_stat_replication. This table is, however, empty on the standby since it contains information about WAL sender processes and not WAL receiver. pg_last_xlog_replay_location and pg_last_xlog_receive_location also continue to be valid even when the streaming replication is down, so they don't help in this case. From online tutorials and PostgreSQL wiki the only way I found is by checking the running processes for wal sender and wal receiver using ps command on Unix systems. The problem is that on Windows, all those processes carry the same name, postgresql.exe. I suppose there should be some parameter to get the db engine as it realizes when the streaming replication is down and it logs that in pg_log files, but I can't seem to find such a parameter. Thank you for your help. -Yamen What are you trying to determine? If it's whether the replication is caught up, I have a small C program that will do that and have posted it before (I can do that again if you'd like.) If it's whether it's up, that's a bit more complex, since you have to define up. For most purposes determining that the offset between the two is less than some value at which you alarm is sufficient, and if you then alarm if you can't reach the master and slave hosts, you then know if the machines are up from a standpoint of reachability on the network as well. -- -- Karl Denninger The Market Ticker ® Cuda Systems LLC -- Network Engineering Design; Content Delivery Platform IP NETCOLOGNE Gesellschaft für Telekommunikation mbH Am Coloneum 9 | 50829 Köln Tel: 0221 -8711 | Fax: 0221 -78711 www.netcologne.de Geschäftsführer: Dr. Hans Konle (Sprecher) Dipl.-Ing. Karl-Heinz Zankel HRB 25580, AG Köln Diese Nachricht (inklusive aller Anhänge) ist vertraulich. Sollten Sie diese Nachricht versehentlich erhalten haben, bitten wir, den Absender (durch Antwort-E-Mail) hiervon unverzüglich zu informieren und die Nachricht zu löschen. Die E-Mail darf in diesem Fall weder vervielfältigt noch in anderer Weise verwendet werden. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] logcheck compatible daily logfile rotation?
Hello I'm trying to figure out how to configure a logcheck compatible logfile rotation i.e. where a) the current logfile always has a fixed name and old files are named like *.1, *.2.gz etc. and b) the current logfile is not truncated but newly created (or else logcheck always warns about possible tempering) So essentially I want syslog style logrotation but not using syslog as that was not recommended (not very reliable in chapter 23.3) and does not look so nice (hostname in every line due to syslog output format, #011 instead of tabs in multiline messages). The builtin log catcher does not rotate files if no %Y or similar is in log_filename and neither can be told to reopen the file with SIGHUP or pg_ctl reload. Using pg_ctl start | rotatelogs, which is recommended in the docs would imply that I modify the /etc/init.d/ script which would work but I hate editing Debian package scritps because such modifications are too easily broken on updates. At the moment I'm stuck with syslog and a custom rsyslog template but still, I'm curious what others are doing! bye, -christian- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Correlation in pg_stats
Hello
Do you want to search for tables with many unordered rows?
SELECT
schemaname, tablename, correlation
FROM
pg_stats
WHERE
abs(correlation) 0.2 and
schemaname not in ('pg_catalog', 'information_schema')
;
Or do you want to put the table in order?
BEGIN;
CREATE TABLE tbl_test_new (LIKE tbl_test INCLUDING ALL);
INSERT INTO tbl_test_new SELECT * FROM tbl_test ORDER BY id;
DROP TABLE tbl_test;
TABLE tbl_test_new RENAME TO tbl_test;
COMMIT;
If you really want to get a correlation of near 0 try
INSERT INTO tbl_test (name) SELECT random() FROM generate_series(1, 100);
bye,
-christian-
Am Mon, 26 Nov 2012 01:52:21 -0800 (PST)
schrieb classical_89 luongnx...@gmail.com:
Hi,First sorry for my bad English :D.I'm new in PostgreSQL and
database .Now i'm researching about statistic correlation .I want to
make a example with the table -assume that I have tbl_test table and
two column - id name - and do something to get correlation of a
column is near 0 (zero).What step can i do ?
Thanks for your support :)
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Correlation-in-pg-stats-tp5733524.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 9.1 to 9.2 requires a dump/reload?
Hello Can you remember where did you read that? There is no mention of GIST on http://www.postgresql.org/docs/9.2/static/upgrading.html and a database which uses GIST indexes *seems* to work just finde after upgrading with pg_upgrade. bye, -christian- Am Mon, 22 Oct 2012 15:02:13 -0700 schrieb Lonni J Friedman netll...@gmail.com: pg_upgrade has worked fine for several releases. I believe that the only time when pg_upgrade isn't a viable option is for some types of GIST indices. On Mon, Oct 22, 2012 at 2:55 PM, Nikolas Everett nik9...@gmail.com wrote: I was just looking at http://www.postgresql.org/docs/devel/static/release-9-2.html and it mentioned that a dump/reload cycle was required to upgrade from a previous release. I just got done telling some of my coworkers that PG had been bitten by this enough times that they were done with it. Am I wrong? Is this normal? I see that pg_upgrade is an option. Having never used how long should I expect pg_upgrade to take? Obviously we'll measure it in our environment, but it'd be nice to have a ballpark figure. Nik -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How and for what to use SP-GIST?
Hello The release notes of PostgreSQL 9.2 praise SP-GIST as a major feature but I cannot find any information on how to actually use it. The main documentation seems unusual low level and the examples chapter just refers to the source code. The only examples I can find there are the regression tests. In those I stumbled across the ~=~ operator. How does it differ from the normal =? (kind of hard to google for this string :)) SP-GIST should be good at suffix search, I read. What exactly is meant here? I assume its not suffix as in LIKE '%foo'. I'm interested because I heavily use the prefix_range extension for phone number searching. Could I somehow benefit from a SP-GIST suffix index (maybe by reversing my numbers)? thanks for any comments! bye, -christian- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] When do archived WAL files gets removed after wal_keep_segments changed?
Hello I run two PostgreSQL servers in a master-slave setup and set wal_keep_segments=1000 on the master to allow long downtimes on the slave. Meanwhile the disk got fuller than I estimated and I changed the config to wal_keep_segments=500 and restarted the server afterwards. Yet, the number of WAL segments in my archive dir was still at 1000! I tried a random mix of VACUUM, CHECKPOINT, pg_start_backup(), pg_stop_backup(), server restarts, googling and just plain waiting but the number of archived WAL segments just increased to 1018. Although I understand that I may safely delete those WAL files that are older than the last .backup file I wonder if that shouldn't be happen automatically. Why doesn't it? Relevant non-default settings from the master with version 9.1.6: wal_level = hot_standby archive_mode = on archive_command = 'test ! -f /srv/postgresql-archivedir/%f cp %p /srv/postgresql-archivedir/%f' wal_keep_segments = 500 hot_standby = on bye, -christian- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Why is the wrong index used? (with gist index)
Hello
Just because I added a LIMIT 1 to the following query, PostgreSQL decided to
use a different index which gives a far worse performance :-(
Probably it gets a bit confused because I use the custom index from the prefix
extension (https://github.com/dimitri/prefix/blob/master/README.txt).
If it's impossible for PostgreSQL to make a correct judgement here, can I force
it to use a certain index in cases where I know better?
The index was created as follows, but the additional gist_prefix_range_ops
parameter does not seem to have any effect:
CREATE INDEX destinations_nr_gist_idx ON destinations USING gist (nr
gist_prefix_range_ops);
The table is 3-4GB big and contains some million rows:
devel=# ANALYZE VERBOSE destinations;
INFO: analyzing public.destinations
INFO: destinations: scanned 3 of 196069 pages, containing 3205481 live
rows and 441 dead rows; 3 rows in sample, 20948720 estimated total rows
devel=# explain analyze SELECT prefix FROM destinations WHERE nr @
'22116804109' ORDER BY length(nr) desc;
QUERY PLAN
-
Sort (cost=63182.14..63234.51 rows=20949 width=22) (actual time=0.277..0.278
rows=2 loops=1)
Sort Key: (length(nr))
Sort Method: quicksort Memory: 25kB
- Bitmap Heap Scan on destinations (cost=817.08..61678.57 rows=20949
width=22) (actual time=0.264..0.269 rows=2 loops=1)
Recheck Cond: (nr @ '22116804109'::prefix_range)
- Bitmap Index Scan on destinations_nr_gist_idx (cost=0.00..811.84
rows=20949 width=0) (actual time=0.253..0.253 rows=2 loops=1)
Index Cond: (nr @ '22116804109'::prefix_range)
Total runtime: 0.315 ms
GOOD!
devel=# explain analyze SELECT prefix FROM destinations WHERE nr @
'22116804109' ORDER BY length(nr) desc LIMIT 1;
QUERY PLAN
Limit (cost=0.00..3481.06 rows=1 width=22) (actual time=689.413..689.414
rows=1 loops=1)
- Index Scan Backward using destinations_nr_length_idx on destinations
(cost=0.00..72924752.20 rows=20949 width=22) (actual time=689.410..689.410
rows=1 loops=1)
Filter: (nr @ '22116804109'::prefix_range)
Total runtime: 689.437 ms
^^ BAD!
devel=# \d+ destinations;
Table public.destinations
Column|Type | Modifiers
| Storage | Description
-+-+---+--+
id | integer | not null default
nextval('destinations_id_seq'::regclass) | plain|
nr | prefix_range| not null
| plain|
prefix | text| not null
| extended |
[...]
Indexes:
destinations_pkey PRIMARY KEY, btree (id)
destinations_nr_unique_idx UNIQUE, btree (nr)
destinations_nr_gist_idx gist (nr)
destinations_nr_length_idx btree (length(nr))
destinations_prefix_idx btree (prefix)
Has OIDs: no
bye,
-christian-
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
