Re: [HACKERS] bind variables, soft vs hard parse
On Mon, Nov 21, 2005 at 09:14:33PM +0100, Marcus Engene wrote: Jim C. Nasby wrote: It might be more useful to look at caching only planning and not parsing. I'm not familiar with the output of the parsing stage, but perhaps that could be hashed to use as a lookup into a cache of planned queries. I suspect that would remove issues of different search_paths. A really stupid question, in the cached query-string, wouldn't it be possible to add the env specifics? Ie the string to check against is something like search_paths=...\n SELECT ... Or would there be too much stuff to append/prepend? It's probably possible, but the thing is, afaik parsing just isn't a bottleneck, so it's just not worth messing with that phase. If you do end up with some super-complex query that does have a non-trivial parse time, I believe views store a pre-parsed representation of the view definition (otherwise there'd be issues with changing search_path between when you create a view and when you use it), so you could effectively cache something by just stuffing it into a view. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] bind variables, soft vs hard parse
Jim C. Nasby wrote: It might be more useful to look at caching only planning and not parsing. I'm not familiar with the output of the parsing stage, but perhaps that could be hashed to use as a lookup into a cache of planned queries. I suspect that would remove issues of different search_paths. A really stupid question, in the cached query-string, wouldn't it be possible to add the env specifics? Ie the string to check against is something like search_paths=...\n SELECT ... Or would there be too much stuff to append/prepend? Best regards, Marcus ---(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 to the mailing list cleanly
Re: [HACKERS] bind variables, soft vs hard parse
On Wed, Nov 16, 2005 at 10:19:21AM +0100, Marcus Engene wrote: Martijn van Oosterhout wrote: This sql cache I think is a really good thing. Is there a reason Postgres hasn't got it? Would it be very hard to implement? From a naive perspective; make a hashvalue from the sql-string to quickly find the cached one, a last used-list for keeping track of which to delete when cache full etc seems close to trivial. Does the architecture/internal flow make it hard actually reuse the query data structure? It's hard to reuse the structure. Also, things like search_path mean that the same query text can mean completely different things in different backends. Most of the time it's planning that dominates, not parsing so storing just the parser output seems somewhat useless. Of course I didn't mean only the parse was to be saved. The planning goes there too. It might be more useful to look at caching only planning and not parsing. I'm not familiar with the output of the parsing stage, but perhaps that could be hashed to use as a lookup into a cache of planned queries. I suspect that would remove issues of different search_paths. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(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 to the mailing list cleanly
Re: [HACKERS] bind variables, soft vs hard parse
Douglas McNaught wrote: Which will be the same as the second call. There is quite a big difference in performance using bind variables. Does Postgres work the same? Where can I go for more info? You can do this (or close to it) but you need to explicitly PREPARE the query (or use the protocol-level prepare, which some client libraries will do for you). See the SQL documentation for PREPARE. -Doug Hi, But this is of no use in a web-context. According to the docs, this prepare is per session. This sql cache I think is a really good thing. Is there a reason Postgres hasn't got it? Would it be very hard to implement? From a naive perspective; make a hashvalue from the sql-string to quickly find the cached one, a last used-list for keeping track of which to delete when cache full etc seems close to trivial. Does the architecture/internal flow make it hard actually reuse the query data structure? Thanks for the answer. Best regards, Marcus ---(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 to the mailing list cleanly
Re: [HACKERS] bind variables, soft vs hard parse
On Wed, Nov 16, 2005 at 09:56:44AM +0100, Marcus Engene wrote: Douglas McNaught wrote: You can do this (or close to it) but you need to explicitly PREPARE the query (or use the protocol-level prepare, which some client libraries will do for you). See the SQL documentation for PREPARE. But this is of no use in a web-context. According to the docs, this prepare is per session. Unless you use something like pgpool, in which case a single session may include multiple requests. This sql cache I think is a really good thing. Is there a reason Postgres hasn't got it? Would it be very hard to implement? From a naive perspective; make a hashvalue from the sql-string to quickly find the cached one, a last used-list for keeping track of which to delete when cache full etc seems close to trivial. Does the architecture/internal flow make it hard actually reuse the query data structure? It's hard to reuse the structure. Also, things like search_path mean that the same query text can mean completely different things in different backends. Most of the time it's planning that dominates, not parsing so storing just the parser output seems somewhat useless. Unless you've thought of a new way to do it. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. pgpP8shUwfFhk.pgp Description: PGP signature
Re: [HACKERS] bind variables, soft vs hard parse
Christopher Kings-Lynne wrote: Oracle recently gave some money to Zend to make proper Oracle support for PHP. In that interface they use bind variables. Apart from greater speed, sqlinjection becomes history as well. I did the same for PostgreSQL for PHP 5.1. http://au3.php.net/manual/en/function.pg-query-params.php Chris Brilliant! I'll upgrade to 5.1 for this reason alone! Best regards, Marcus ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] bind variables, soft vs hard parse
Martijn van Oosterhout wrote: But this is of no use in a web-context. According to the docs, this prepare is per session. Unless you use something like pgpool, in which case a single session may include multiple requests. ok. Good point. This sql cache I think is a really good thing. Is there a reason Postgres hasn't got it? Would it be very hard to implement? From a naive perspective; make a hashvalue from the sql-string to quickly find the cached one, a last used-list for keeping track of which to delete when cache full etc seems close to trivial. Does the architecture/internal flow make it hard actually reuse the query data structure? It's hard to reuse the structure. Also, things like search_path mean that the same query text can mean completely different things in different backends. Most of the time it's planning that dominates, not parsing so storing just the parser output seems somewhat useless. Of course I didn't mean only the parse was to be saved. The planning goes there too. Thanks for the explanation. Have a nice day, The same! Marcus ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] bind variables, soft vs hard parse
Martijn van Oosterhout kleptog@svana.org writes: Unless you use something like pgpool, in which case a single session may include multiple requests. Actually, I've found pgpool to be no better when it comes to using real prepared queries---there's no guarantee that any given request is going to connect to the same pgpool process as before, so it won't have the prepared request. Mike ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] bind variables, soft vs hard parse
Marcus Engene [EMAIL PROTECTED] writes: Which will be the same as the second call. There is quite a big difference in performance using bind variables. Does Postgres work the same? Where can I go for more info? You can do this (or close to it) but you need to explicitly PREPARE the query (or use the protocol-level prepare, which some client libraries will do for you). See the SQL documentation for PREPARE. -Doug ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] bind variables, soft vs hard parse
PostgreSQL combines both parses into one, so every new query is effectively a hard parse (unless it's prepared, then there is no parse or optimization at all). On Tue, Nov 15, 2005 at 07:33:46PM +0100, Marcus Engene wrote: Hi list. I've mostly used Oracle in the past, but for a web-project I took the opportunity to try Postgres. When a select is done in Oracle, it first checks if the select is cached (ie parsed tree, optimizer choices such). It does this by [functionality equal to] a byte to byte compare with the other sql strings. select a from b where c = 1 select a from b where c = 2 ...will thus force a hard parse on the second select. But if using bind variables it wont as the string stored is something like select a from b where c = ? Which will be the same as the second call. There is quite a big difference in performance using bind variables. Does Postgres work the same? Where can I go for more info? Oracle recently gave some money to Zend to make proper Oracle support for PHP. In that interface they use bind variables. Apart from greater speed, sqlinjection becomes history as well. Best regards, Marcus ---(end of broadcast)--- TIP 6: explain analyze is your friend -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] bind variables, soft vs hard parse
In some cases, Oraclewill also replace literals with bind variables so that it can perform a sort-of-bind-value soft parse later. On 11/15/05, Jim C. Nasby [EMAIL PROTECTED] wrote: PostgreSQL combines both parses into one, so every new query iseffectively a hard parse (unless it's prepared, then there is no parse or optimization at all).On Tue, Nov 15, 2005 at 07:33:46PM +0100, Marcus Engene wrote: Hi list. I've mostly used Oracle in the past, but for a web-project I took the opportunity to try Postgres. When a select is done in Oracle, it first checks if the select is cached (ie parsed tree, optimizer choices such). It does this by [functionality equal to] a byte to byte compare with the other sql strings. select a from b where c = 1 select a from b where c = 2 ...will thus force a hard parse on the second select. But if using bind variables it wont as the string stored is something like select a from b where c = ? Which will be the same as the second call. There is quite a big difference in performance using bind variables. Does Postgres work the same? Where can I go for more info? Oracle recently gave some money to Zend to make proper Oracle support for PHP. In that interface they use bind variables. Apart from greater speed, sqlinjection becomes history as well. Best regards, Marcus ---(end of broadcast)--- TIP 6: explain analyze is your friend--Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]Pervasive Softwarehttp://pervasive.comwork: 512-231-6117vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461---(end of broadcast)---TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] bind variables, soft vs hard parse
Oracle recently gave some money to Zend to make proper Oracle support for PHP. In that interface they use bind variables. Apart from greater speed, sqlinjection becomes history as well. I did the same for PostgreSQL for PHP 5.1. http://au3.php.net/manual/en/function.pg-query-params.php Chris ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
