[SQL] pg_dump and sequences
Hi, i am using postgreSQL 7.2.1 on a redhat 7.2 (kernel
2.4.9-31,glibc-2.2.4-24).
It seems that pg_dump -t "tablename" dumps correctly the sequence of a
table's column,when the column is named after "id".
For example,
dynacom=# CREATE TABLE foo2 (fid serial NOT NULL UNIQUE PRIMARY KEY,id
serial);
[postgres@pc216 ~]% pg_dump -t foo2 > foo2DUMP.sql
[postgres@pc216 ~]% cat foo2DUMP.sql
--
-- Selected TOC Entries:
--
\connect - postgres
--
-- TOC Entry ID 2 (OID 2009757)
--
-- Name: foo2_id_seq Type: SEQUENCE Owner: postgres
--
CREATE SEQUENCE "foo2_id_seq" start 1 increment 1 maxvalue
9223372036854775807 minvalue 1 cache 1;
--
-- TOC Entry ID 4 (OID 2009759)
--
-- Name: foo2 Type: TABLE Owner: postgres
--
CREATE TABLE "foo2" (
"fid" integer DEFAULT nextval('"foo2_fid_seq"'::text) NOT NULL,
"id" integer DEFAULT nextval('"foo2_id_seq"'::text) NOT NULL,
Constraint "foo2_pkey" Primary Key ("fid")
);
--
-- Data for TOC Entry ID 6 (OID 2009759)
--
-- Name: foo2 Type: TABLE DATA Owner: postgres
--
COPY "foo2" FROM stdin;
\.
--
-- TOC Entry ID 5 (OID 2009762)
--
-- Name: "foo2_id_key" Type: INDEX Owner: postgres
--
CREATE UNIQUE INDEX foo2_id_key ON foo2 USING btree (id);
--
-- TOC Entry ID 3 (OID 2009757)
--
-- Name: foo2_id_seq Type: SEQUENCE SET Owner: postgres
--
SELECT setval ('"foo2_id_seq"', 1, false);
--
Any ideas??
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel:+30-10-8981112
fax:+30-10-8981877
email: [EMAIL PROTECTED]
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] pg_dump and sequences
On Tue, 23 Jul 2002, Achilleus Mantzios wrote: Just a correction > Hi, i am using postgreSQL 7.2.1 on a redhat 7.2 (kernel > 2.4.9-31,glibc-2.2.4-24). > > It seems that pg_dump -t "tablename" dumps correctly the sequence of a > table's column,when the column is named after "id". > I meant "when ONLY" the column is named after "id", whereas it does not dump the sequence when the column is named after something different than "id". -- Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Queries not using Index
(sorry if you get this twice, my first post bounced since I did not use the subscribed email account. I assume that those messages get discarded...) Greetings, I suppose I should have sent this to pgsql-bugs maybe? I would appreciate it if anybody could help me out. I can't figure out what is going on here... snet=# select version(); version - PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.96 snet=# \d t2002_06 Table "t2002_06" Column | Type | Modifiers -+--+--- station | character varying(5) | valid | timestamp with time zone | tmpf| smallint | dwpf| smallint | drct| smallint | sknt| real | pday| real | pmonth | real | srad| real | relh| real | alti| real | Indexes: t2002_06_station_idx, t2002_06_tmpf_idx snet=# \d t2002_06_station_idx; Index "t2002_06_station_idx" Column | Type -+-- station | character varying(5) snet=# select count(valid) from t2002_06; count - 1513895 snet=# vacuum analyze t2002_06; VACUUM snet=# vacuum t2002_06; VACUUM snet=# explain SELECT * from t2002_06 WHERE station = 'SGLI4'; NOTICE: QUERY PLAN: Seq Scan on t2002_06 (cost=0.00..35379.69 rows=35564 width=47) EXPLAIN Shouldn't this be an index scan? I hope that I am not doing something stupid, although I probably am :( I even just tried this drop index t2002_06_station_idx; vacuum analyze t2002_06; create index t2002_06_station_idx on t2002_06(station); vacuum analyze t2002_06; And I still get a Seq Scan. Augh Thanks, Daryl ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Queries not using Index
Greetings, I suppose I should have sent this to pgsql-bugs maybe? I would appreciate it if anybody could help me out. I can't figure out what is going on here... snet=# select version(); version - PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.96 snet=# \d t2002_06 Table "t2002_06" Column | Type | Modifiers -+--+--- station | character varying(5) | valid | timestamp with time zone | tmpf| smallint | dwpf| smallint | drct| smallint | sknt| real | pday| real | pmonth | real | srad| real | relh| real | alti| real | Indexes: t2002_06_station_idx, t2002_06_tmpf_idx snet=# \d t2002_06_station_idx; Index "t2002_06_station_idx" Column | Type -+-- station | character varying(5) snet=# select count(valid) from t2002_06; count - 1513895 snet=# vacuum analyze t2002_06; VACUUM snet=# vacuum t2002_06; VACUUM snet=# explain SELECT * from t2002_06 WHERE station = 'SGLI4'; NOTICE: QUERY PLAN: Seq Scan on t2002_06 (cost=0.00..35379.69 rows=35564 width=47) EXPLAIN Shouldn't this be an index scan? I hope that I am not doing something stupid, although I probably am :( I even just tried this drop index t2002_06_station_idx; vacuum analyze t2002_06; create index t2002_06_station_idx on t2002_06(station); vacuum analyze t2002_06; And I still get a Seq Scan. Augh Thanks, Daryl ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Queries not using Index
"Daryl Herzmann" <[EMAIL PROTECTED]> wrote: > snet=# select count(valid) from t2002_06; > count > - > 1513895 > snet=# explain SELECT * from t2002_06 WHERE station = 'SGLI4'; > NOTICE: QUERY PLAN: > > Seq Scan on t2002_06 (cost=0.00..35379.69 rows=35564 width=47) Can you do the following query for better understand your situation ? select count(*) from t2002_06 where station = 'SGLI4'; select count(*) from t2002_06; Ciao Gaetano. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Scan SQL
Hi I need to make a parser in a sql to get tables and fields to verify the privileges. Do anybody know software, function or anything that can help me? I found software to create a compiler and generate the source in delphi, but I need a sql specification (BNF, EBNF) and I do not know where to find it. Thanks Sandro ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Editor for pgsql
On Tuesday 23 July 2002 07:45, Roberto Mello wrote:
> On Mon, Jul 22, 2002 at 05:41:39PM -0700, Josh Berkus wrote:
> > I tried to install Tora, but the build blew up since I don't have Oracle
> > installed. Any tips?
>
> I just use the Debian packages (/me hugs Debian).
>
> AFAIK, all you have to do is compile with the appropriate flags so it
> doesn't try to build Oracle support (you need a full Oracle installation),
> and also MySQL support.
>
> You also need to have the PostgreSQL loadable Qt 3 module installed before
> compiling, plus Qt 3 headers and such.
>
> You could try downloading the binary Debian packages from
> packages.debian.org ("unstable" distribution) and unpacking them (Debian
> packages are just "ar" packages with extra headers).
I just compiled the latest version(tora-1.3.6.1) right now(on Mandrake-8.1
with KDE-3.0.2 ant qt-3.0.4) with the following options to configure:
./configure --without-oracle --without-kde
make
su -c "make install"
This compiles and installes just fine to /usr/local/bin with PostgreSQL
support.
--
Andreas Joseph Krogh (Senior Software Developer) <[EMAIL PROTECTED]>
- There are 10 kinds of people in the world, those that can do binary
arithmetic and those that can't.
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Queries not using Index
On Mon, 22 Jul 2002, Daryl Herzmann wrote: > snet=# explain SELECT * from t2002_06 WHERE station = 'SGLI4'; > NOTICE: QUERY PLAN: > > Seq Scan on t2002_06 (cost=0.00..35379.69 rows=35564 width=47) > > EXPLAIN What does explain show if you do set enable_seqscan=off; ? It's possible that it's estimating a greater cost for the index scan. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Editor for pgsql
Folks, > This compiles and installes just fine to /usr/local/bin with > PostgreSQL > support. Thanks, I'll try it. -Josh Berkus ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Scan SQL
Sandro, > I need to make a parser in a sql to get tables and fields to verify > the > privileges. Do anybody know software, function or anything that can > help me? I probably can, but I'm not quite sure what you're asking for. Could you explain at greater length, maybe with some examples? -Josh Berkus ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] [GENERAL] No command history in psql
ldd /path/to/psql should tell you if its finding the readline libraries. you might also want to do ldconfig -v | grep 'history' and ldconfig -v | grep 'readline' to see what libraries you have. you can then verify that the libs you have are actually where your telling pg they are supposed to be. Robert Treat On Tue, 2002-07-23 at 10:53, Carmen Wai wrote: > Hello: > > I am upgrading to postgresql version 7.2.1. I found that the psql has not > included the readline library automatically and doesn't have any readline > and history command function. So I configure the postgresql with option > --with-includes=/usr/local/include and --with-libs=/usr/libs/ so that it > should be able to search for the corresponding library and header files > (libreadline.a, history.h, readline.h). But it still fails Does anyone > get any idea?? > > Thanks a lot! > > _ > Send and receive Hotmail on your mobile device: http://mobile.msn.com > > > ---(end of broadcast)--- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to [EMAIL PROTECTED] so that your > message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Queries not using Index
>On Tue, 23 Jul 2002, Daryl Herzmann wrote: > >> snet=# explain analyze select * from t2002_06 WHERE station = 'SAMI4'; >> NOTICE: QUERY PLAN: >> >> Seq Scan on t2002_06 (cost=0.00..35379.69 rows=34979 width=47) (actual >> time=67.89..3734.93 rows=38146 loops=1) >> Total runtime: 3748.33 msec >> >> EXPLAIN >> >> snet=# set enable_seqscan=off; >> SET VARIABLE >> snet=# explain analyze select * from t2002_06 WHERE station = 'SAMI4'; >> NOTICE: QUERY PLAN: >> >> Index Scan using t2002_06_station_idx on t2002_06 (cost=0.00..132124.96 >> rows=34979 width=47) (actual time=72.03..298.85 rows=38146 loops=1) >> Total runtime: 317.76 msec > >Looks like the estimated cost is way divorced from reality. Is the >34979 row estimate even realistic and how well ordered is the table >(actually output from pg_statistic would be good as well :) ). Thanks for the help! I am not sure if I can answer your questions. I will try :) I believe the row estimate is realistic based on this value. snet=# select count(*) from t2002_06 WHERE station = 'SAMI4'; count --- 38146 I am really sorry, but I don't know what to output from pg_statistic. I searched around on the Internet and was not sure what to send you from this table. Sorry :( Thanks! Daryl ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Queries not using Index
On Tue, 23 Jul 2002, Daryl Herzmann wrote: > > >On Tue, 23 Jul 2002, Daryl Herzmann wrote: > > > >> snet=# explain analyze select * from t2002_06 WHERE station = 'SAMI4'; > >> NOTICE: QUERY PLAN: > >> > >> Seq Scan on t2002_06 (cost=0.00..35379.69 rows=34979 width=47) (actual > >> time=67.89..3734.93 rows=38146 loops=1) > >> Total runtime: 3748.33 msec > >> > >> EXPLAIN > >> > >> snet=# set enable_seqscan=off; > >> SET VARIABLE > >> snet=# explain analyze select * from t2002_06 WHERE station = 'SAMI4'; > >> NOTICE: QUERY PLAN: > >> > >> Index Scan using t2002_06_station_idx on t2002_06 (cost=0.00..132124.96 > >> rows=34979 width=47) (actual time=72.03..298.85 rows=38146 loops=1) > >> Total runtime: 317.76 msec > > > >Looks like the estimated cost is way divorced from reality. Is the > >34979 row estimate even realistic and how well ordered is the table > >(actually output from pg_statistic would be good as well :) ). > > Thanks for the help! I am not sure if I can answer your questions. I will > try :) > > I believe the row estimate is realistic based on this value. > > snet=# select count(*) from t2002_06 WHERE station = 'SAMI4'; > count > --- > 38146 > I am really sorry, but I don't know what to output from pg_statistic. I > searched around on the Internet and was not sure what to send you from > this table. Sorry :( Right... sorry about that... select * from pg_statistic where starelid=(select oid from pg_class where relname='t2002_06'; ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Scan SQL
On Tue, Jul 23, 2002 at 10:11:18AM -0300, Sandro Joel Eller wrote: > Hi > > I need to make a parser in a sql to get tables and fields to verify the > privileges. Do anybody know software, function or anything that can help me? > > I found software to create a compiler and generate the source in delphi, but I > need a sql specification (BNF, EBNF) and I do not know where to find it. You could look at how PostgreSQL does its parsing. The source code is open. -Roberto -- +| http://fslc.usu.edu/ USU Free Software & GNU/Linux Club |--+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net/ http://www.sdl.usu.edu/ - Space Dynamics Lab, Developer I to¤d yo¤, "Never¤touch ¤he flo¤py di¤k su¤face!" ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Queries not using Index
Hi! Thanks for the continued help. I have attached the results of your request. Thank you! Daryl >Right... sorry about that... >select * from pg_statistic where starelid=(select oid from pg_class > where relname='t2002_06'; sql.out.gz Description: GNU Zip compressed data ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Misunderstanding about your article entitled: Episode 2:
Mike, > "Technical Note: as of PostgreSQL 7.2.1, DEFERRABLE UNIQUE > constraints are > not supported -- which means that you should not declare unique > constraints > on columns whose values will be frequently re-arranged, like custom > sort > order columns, even if the values in these columns are unique." > > Perhaps you mean for columns that are part of an order by clause Or > you mean > something much more subtle...where the value for a column is set to A > then to > B then to C... all the time? The latter. For example, if you have a table like: create table articles ( article_id INT, title VARCHAR(100), summary varchar(300), content TEXT, sort_order INT ); ... where sort_order is a user-maintained custom sort order which is different from the alpha sort. In this case, the values in sort_order will be unique whenever re-ordering is complete, and this is fairly easy to ensure via triggers. However, while the re-ordering is in process, there will temporarily be some non-unique values while, for example, the article.sort_order #122 is being swapped with article.sort_order #123. When deferrable unique constraints are implemented (maybe Postgres 8.0?) then the uniqueness check will happen only at the *end* of a transaction, and a unique index on this kind of a column will be permitted. Currently, it raises an error preventing column re-ordering. -Josh Berkus ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Scan SQL
On Tue, 23 Jul 2002, Roberto Mello wrote: > On Tue, Jul 23, 2002 at 10:11:18AM -0300, Sandro Joel Eller wrote: > > Hi > > > > I need to make a parser in a sql to get tables and fields to verify the > > privileges. Do anybody know software, function or anything that can help me? > > > > I found software to create a compiler and generate the source in delphi, but I > > need a sql specification (BNF, EBNF) and I do not know where to find it. > > You could look at how PostgreSQL does its parsing. The source code is > open. > > -Roberto Check out src/backend/parser/*.y for full YACC grammars. Joshua b. Jore ; http://www.greentechnologist.org ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] 7.3 features and enhancements
Read the TODO list on the developer's web site. All the dashed items are ready for 7.3. --- mark carew wrote: > Hi All, > > Can somebody direct me to a list of the above. Would be nice to know in > advance of its release. > > Regards Mark > > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] FATAL 1
It means something is really wacked out about your installation. What does the server log show? --- Ian Cass wrote: > Couldn't find any mention of this anywhere. Anyone any idea what it means? A > db stop/start seems to have cured it. > > postgres@judas:~$ psql master > psql: FATAL 1: fixrdesc: no pg_class entry for pg_class > > postgres@judas:~$ psql -V > psql (PostgreSQL) 7.2.1 > contains support for: readline, history, multibyte > Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group > Portions Copyright (c) 1996, Regents of the University of California > Read the file COPYRIGHT or use the command \copyright to see the > usage and distribution terms. > > postgres@judas:~$ uname -a > Linux judas 2.4.18 #1 Fri May 3 11:45:59 UTC 2002 i686 unknown > > -- > Ian Cass > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Queries not using Index
On Tue, 23 Jul 2002, Daryl Herzmann wrote: > Hi! > > Thanks for the continued help. > > I have attached the results of your request. Thank you! Hmm, when the data was put in, was it put in where the same value would be bunched up? IIRC that's a case the optimizer won't realize if the data isn't ordered but merely bunched together that'd cause it to over-estimate the cost of an index scan. Clustering on the index might help, but cluster drops alot of info about the table, so you have to be careful. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Queries not using Index
Good evening. On Tue, 23 Jul 2002, Stephan Szabo wrote: >Hmm, when the data was put in, was it put in where the same value >would be bunched up? I inserted the data via 30 "COPY t2002_06 from stdin" (one per day) So it was grouped by station and then day for each insert. (My script dumped the data from each station for the day and then repeated for each station and then finally dumped the entire day into the DB. Are you saying that this process has tricked pgsql into not believing it needs to use an INDEX? Sorry for my ignorance here. I have done similar processes with PG7.1.2 and it seemed to use the INDEX. In fact, I just repeated the dumping scripts on a machine with 7.1.2 and the "explain select" reports to be using the Index Scan. H >IIRC that's a case the optimizer won't realize if the data isn't ordered >but merely bunched together that'd cause it to over-estimate the cost of >an index scan. Clustering on the index might help, but cluster drops alot >of info about the table, so you have to be careful. Thanks for the info. I am off to read about how to do clustering! Thanks! Daryl ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Queries not using Index
> I inserted the data via 30 "COPY t2002_06 from stdin" (one per > day) So it > was grouped by station and then day for each insert. (My script dumped > the data from each station for the day and then repeated for each station > and then finally dumped the entire day into the DB. Are you saying that > this process has tricked pgsql into not believing it needs to use an > INDEX? Sorry for my ignorance here. I have done similar processes with > PG7.1.2 and it seemed to use the INDEX. > > In fact, I just repeated the dumping scripts on a machine with 7.1.2 and > the "explain select" reports to be using the Index Scan. H You _have_ actually run ANALYZE on the table, right? Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Queries not using Index
Hi, >You _have_ actually run ANALYZE on the table, right? snet=# vacuum analyze t2002_06; VACUUM snet=# vacuum analyze; VACUUM snet=# explain analyze select * from t2002_06 WHERE station = 'SAMI4'; NOTICE: QUERY PLAN: Seq Scan on t2002_06 (cost=0.00..35379.69 rows=35169 width=47) (actual time=20.51..1717.78 rows=38146 loops=1) Total runtime: 1730.63 msec EXPLAIN snet=# set enable_seqscan=off; SET VARIABLE snet=# explain analyze select * from t2002_06 WHERE station = 'SAMI4'; NOTICE: QUERY PLAN: Index Scan using t2002_06_station_idx on t2002_06 (cost=0.00..132773.85 rows=35169 width=47) (actual time=74.86..299.53 rows=38146 loops=1) Total runtime: 313.42 msec EXPLAIN Any thoughts? I am sorry to be causing all this trouble. I just want my queries to voom-voom!! Interestingly enough, I see that the SEQ SCAN is now estimated at 1730.63, when I first posted to this list, it was 3900.00 or so. E Thanks, Daryl ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Queries not using Index
Have you tried playing with the statistics gatherer? >From the ANALYZE docs: "The extent of analysis can be controlled by adjusting the per-column statistics target with ALTER TABLE ALTER COLUMN SET STATISTICS (see ALTER TABLE). The target value sets the maximum number of entries in the most-common-value list and the maximum number of bins in the histogram. The default target value is 10, but this can be adjusted up or down to trade off accuracy of planner estimates against the time taken for ANALYZE and the amount of space occupied in pg_statistic. In particular, setting the statistics target to zero disables collection of statistics for that column. It may be useful to do that for columns that are never used as part of the WHERE, GROUP BY, or ORDER BY clauses of queries, since the planner will have no use for statistics on such columns. " Just a thought... Also, what is the result of: select indexdef from pg_indexes where indexname='t2002_06_station_idx'; > Any thoughts? I am sorry to be causing all this trouble. I just want my > queries to voom-voom!! Interestingly enough, I see that the SEQ SCAN is > now estimated at 1730.63, when I first posted to this list, it > was 3900.00 > or so. E It's no trouble. Cases where the planner fails are essential to improving the planner. Ideally this query should use your index automatically... Chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Queries not using Index
Hi! :) On Wed, 24 Jul 2002, Christopher Kings-Lynne wrote: >Have you tried playing with the statistics gatherer? Nope. I will look at the docs some and play around. This machine is not fully production yet. :) >Also, what is the result of: >select indexdef from pg_indexes where indexname='t2002_06_station_idx'; snet=# select indexdef from pg_indexes where indexname='t2002_06_station_idx'; indexdef - CREATE INDEX t2002_06_station_idx ON t2002_06 USING btree (station) >It's no trouble. Cases where the planner fails are essential to improving >the planner. Ideally this query should use your index automatically... Thanks! I have the ~exact~ same database on another machine with PG 7.1.2 and it uses the Index Scan without tweaking. I have never had troubles with indexes up until this table/database. I have got another database on the same 7.2.1 machine. It has very similar data and the same index. It has no trouble defaulting to use the Index. awos=# explain analyze select * from t1999_06 WHERE station = 'SAMI4'; NOTICE: QUERY PLAN: Index Scan using t1999_06_stn_idx on t1999_06 (cost=0.00..25859.88 rows=36544 width=53) (actual time=152.94..152.94 rows=0 loops=1) Total runtime: 153.03 msec EXPLAIN Augh. Puzzling. Thanks everyone for the help! You all rock! Daryl ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
