Re: [GENERAL] Which query is less expensive / faster?
Thanks for the reply... but which query will be faster and less expensive? I don't have a table now with 4 million rows, but I'm thinking of building such a table. Querying it would return 1 row. The alternative is to query an existing table of 200k rows, and return 800 rows. Option 1: Query a table of 4 million rows, on 4 indexed columns. It will return 1 row: SELECT field1, field2, field3, field4 FROM tablea WHERE field1 = $1 AND field2 = $2 AND field3 = $3 AND field4 = $4 Option 2: Query a table of 200,000 rows on 1 indexed column. It will return 800 rows: SELECT * FROM tableb WHERE field1 = $1 Which one is going to return results the fastest, with the least expense to the database server? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Tzahi Fadida Sent: Thursday, February 24, 2005 5:18 PM To: 'Postgres Coder'; pgsql-general@postgresql.org Subject: Re: [GENERAL] Which query is less expensive / faster? I am not an expert to postgres internals but in General: if you have a btree multicolumn index on the 4 fields then it should take around logF(4M). lets guess the F to be 5 so its around 10 ios +1 block read. for the same thing for a hashtable its about the same or less. if you have any subset of the fields indexed with a btree it costs logF(4M) + all the blocks with those subset which is still better than a sequential scan. another possibility which requires careful analyze of the frequencies is intersecting all the rows from the 4 separate indices and finding 1 that matches. In any case, when in doubt run the EXPLAIN on your query. see the documentation. Regards, tzahi. > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Postgres > Coder > Sent: Friday, February 25, 2005 1:46 AM > To: pgsql-general@postgresql.org > Subject: [GENERAL] Which query is less expensive / faster? > > > Hi all, > > I have a couple different ways to get results out of my table > structure, but I'm unsure which way is faster or less expensive to > run: > > Option 1: Query a table of 4 million rows, on 4 indexed columns. It > will return 1 row: > > SELECT field1, field2, field3, field4 FROM tablea WHERE field1 = $1 > AND field2 = $2 AND field3 = $3 AND field4 = $4 > > Option 2: Query a table of 200,000 rows on 1 indexed column. > It will return 800 rows: > > SELECT * > FROM tableb > WHERE field1 = $1 > > Which one is going to return results the fastest, with the least > expense to the database server? > > ---(end of > broadcast)--- > TIP 8: explain analyze is your friend > > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) ---(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
[GENERAL] Postgres friendly RegEx?
Anyone care to correct this regex for PostgreSQL? It works in C++ but
Postgres have no love for it:
-{0,1}\d*\.{0,1}\d+\^{0,1}\d*\.{0,1}\d+
This regex accepts (any num)^(pos num) such as:
45.2^3
-45.2^3
10^2.5
Re: [GENERAL] Postgres friendly RegEx?
In Postgres, it appears to be returning false positives:
select * from
(select '52'::varchar As val) d
where d.val ~ '-{0,1}\\d*\\.{0,1}\\d+\\^{0,1}\\d*\\.{0,1}\\d+'
returns a record.
In C++ only such values match: 45.2^3 or -45.2^3 or 10^2.5
On 12/12/06, Tom Lane <[EMAIL PROTECTED]> wrote:
"Jon Asher" <[EMAIL PROTECTED]> writes:
> Anyone care to correct this regex for PostgreSQL? It works in C++ but
> Postgres have no love for it:
> -{0,1}\d*\.{0,1}\d+\^{0,1}\d*\.{0,1}\d+
It works fine in Postgres, AFAICT. Maybe you forgot to double the
backslashes in a string literal? Otherwise, be more specific about
your problem.
regards, tom lane
Re: [GENERAL] Postgres friendly RegEx?
Please ignore, my mistake in the translation to Pg regex !
On 12/12/06, Jon Asher <[EMAIL PROTECTED]> wrote:
In Postgres, it appears to be returning false positives:
select * from
(select '52'::varchar As val) d
where d.val ~ '-{0,1}\\d*\\.{0,1}\\d+\\^{0,1}\\d*\\.{0,1}\\d+'
returns a record.
In C++ only such values match: 45.2^3 or -45.2^3 or 10^2.5
On 12/12/06, Tom Lane <[EMAIL PROTECTED]> wrote:
>
> "Jon Asher" <[EMAIL PROTECTED]> writes:
> > Anyone care to correct this regex for PostgreSQL? It works in C++ but
>
> > Postgres have no love for it:
>
> > -{0,1}\d*\.{0,1}\d+\^{0,1}\d*\.{0,1}\d+
>
> It works fine in Postgres, AFAICT. Maybe you forgot to double the
> backslashes in a string literal? Otherwise, be more specific about
> your problem.
>
>regards, tom lane
>
[GENERAL] EXECUTE INTO on 8.2
I'm seeing some strange behavior with the following code. It compiles and runs but returns an error on the Execute statement: List index out of bounds(0) DECLARE srec record; v_formula varchar; v_result varchar; BEGIN v_formula = 'select 4 as val'; EXECUTE v_formula INTO srec; END; However, the old school version runs w/no problem: BEGIN v_formula = 'select 4 as val'; FOR srec IN EXECUTE v_formula LOOP v_result = srec.val; END LOOP; END; Any idea why the new syntax isn't working? (I'm running 8.2 on a Windows dev box.)
[GENERAL] Basic problem installing TSearch2 (full text search)
Hi all,
I've installed
TSearch2 with some success- my table now contains a tsvector field that's
indexed and I can run full text queries.
However, the trigger
that I created to update the full text index when a row is modified appears to
cause a problem. It's returning the error when I run an Update
statement:
could not find
tsearch config by locale
The general intro
doc to TSearch2 covers this error and explains how to fix it. (http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/tsearch-V2-intro.html)
I thought that I changed the config properly but obviously I'm missing
something. The steps I followed and the values in the system
tables I updated are listed below. I'd appreciate any
input!
/* add
indexed text field and trigger */UPDATE log
SET fti_notes = to_tsvector('default', notes);CREATE INDEX
log_fti_notes_index ON log USING gist(fti_notes);vacuum analyze
log;CREATE TRIGGER trg_log_update_tsvector BEFORE UPDATE OR INSERT ON
log FOR EACH ROW EXECUTE PROCEDURE
tsearch2(fti_notes, notes);/* update
configuration file */SHOW server_encoding;
/* above command
displays: SQL_ASCII */
update pg_ts_cfg set locale = 'SQL_ASCII' where
ts_name = 'default'
SELECT * from pg_ts_cfg
/* above
Select returns the following */
ts_name
prs_name
locale
default
default
SQL_ASCII
/* now test tsearch.
Select statements work! however, update statement with associated
trigger fails /*
update log set notes = 'Evergreen in San Jose
is a nice place.' where log_id =
529
/* returns error
message: could not find tsearch config by locale
*/
[GENERAL] Querying large record sets
Hi, Our new Postgres database includes a table with about 1 mil records. So far, I've been impressed with the performance of queries against 1 mil rows. Unfortunately I just found out that the size of the table will increase to 3 mil records. Can I expect Postgres to return results on a query with a basic join against a 3 mil record table in under 1 sec? The box is a Xenon processor with 512 MB RAM. The table is indexed on an integer field that's also the foreign key in most joins. I'm not familiar with how Postgres scales as your table size grows, so any input would be appreciated... Jon
[GENERAL] Large update operations and performance
I'm running some very large table update operations (on 2 mil + records) and the queries are taking 5 + hours to complete. Is there a way to turn off row-level index updates or something else to make it run more quickly? Jon
